Home > OS >  How can I setup a way to toggle between highlighting cells that match list A to matching list B?
How can I setup a way to toggle between highlighting cells that match list A to matching list B?

Time:03-21

I have a list of data and I want to be able to toggle between highlighting one subset to highlighting another subset. Is there a way to toggle between highlighting subset A to highlighting subset B?

CodePudding user response:

A Data Validation and Conditional Formatting Combination

  • Select cell A2. Select Data > Data Validation > Data Validation and under Allow: choose List and under Source: input 1;2.
  • Select range C2:H11. Select Home > Conditional Formatting > New Rule and select Use a formula to determine which cells to format and in the text box below add the following formula:
=OR(AND($A$2=1,ISNUMBER(MATCH(C2,$J$2:$J$6,0))),AND($A$2=2,ISNUMBER(MATCH(C2,$K$2:$K$6,0))))

and select OK and OK.

  • If you select 1 in cell A2 the numbers from the J column are highlighted, and if you select 2, the numbers from the K column are highlighted.

enter image description here

  • Related