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

Question

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?

Answer 1

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.