Conditional Formatting not enough

Question

I'm trying to set conditional formatting in a specific group of cells J9:N12 with a locked cell I9 (110). In Cells J9:N12 I need the conditional formatting to set 1,2,7,8,13,14 as Black Fill w/ White Text, 3,4,9,10,15,16 as Red Fill w/ Black Text, 5,6,11,12,17,18 as Blue Fill w/ White Text. Then if I Change (I9) to 277 the Fills would change to reflect Brown/Orange/Yellow in place of the Black/Red/Blue.

Yes, I can Conditional Format the cells I need based on the information set in cell (I9) 110 or 277.

The series of numbers I need to format is greater than 120. I cannot set Conditional Formatting for each specific number needed. Minimallistically I need to be able to set Conditional Formatting to around 150-160 numbers.

I don't know if I need to utilize VBA or if Conditional Formatting is the way to go.

Answer 1

You could use conditional formatting for this. I'd be tempted to put the number series in an array and test for a find in the array using MATCH. This way, you'd only need as many conditional formatting rules as there are number series.

I'm not certain, but I believe you can't write an array directly into a conditional format rule, but you could refer to it from something like a range name. In any event, a range name would be easier to manage if you needed to change any of the number series.

For example, in the black/brown case, you could create a range name (Formulas->Define Name), called black-brown, and in the Refers To window, have the formula:

={1,2,7,8,13,14}

Then your conditional formatting rule would be something like:

=AND($I$9=110,NOT(ISNA(MATCH(B2,black_brown,0))))

Note my cell to be tested is in B2 - change that to whatever you need.

You'd just add rules for each of your number series cases, as shown below: