Arrays in Conditional Formatting

**Dancin** · 10-12-2009, 03:20 AM

I have seen similar (old) posts to this, but they did not quite cover what I was after.

I know you cant use Array constants in Conditional Formatting, but I was under the impression you could use array formulas.

If I have a column of randomly generated numbers, how can I get different numbers to have different formatting (numbers/criteria are not sequential).

Its the old roulette results thing, that I am sure is a lot simpler then it looks...

Any help would be greatly appreciated.

**DonkeyOte** · 10-12-2009, 03:26 AM

Can you provide an example ?
Pre XL2007 you're limited to 3 conditional formats + standard for any given cell (more than that and you require VBA)
On a final note - all formulae used in Conditional Formatting / Validation etc are processed as though they are Arrays (ie no need for CTRL + SHIFT + ENTER)

**Dancin** · 10-12-2009, 03:39 AM

for example, say column A has 20 random numbers (spins), and I want the colors of the font to be...

RED for 1;3;5;7;9;12;14;16;18;19;21;23;25;27;30;32;34;36
BLACK for 2;4;6;8;10;11;13;15;17;20;22;24;26;28;29;31;33;35

I originally tried to use:
"formula is" =OR(1;3;5;7;9;12;14;16;18;19;21;23;25;27;30;32;34;36) Then color RED

but this does not work... my brain hurts with all the googling I have done... any Ideas?

Dancin

**DonkeyOte** · 10-12-2009, 03:48 AM

You would have qualify each condition in the OR.

Let's say you're using A1:A20, you would highlight this range (selecting A1 first) and then set up a rule of:

Please Login or Register  to view this content.

For blank you can simplify in so far as it is the 2nd rule

Please Login or Register  to view this content.

The rules are processed akin to an IF, ie as soon as TRUE is returned remaining tests are ignored thus you know if you've made the 2nd test that the numbers must relate to blank given they are not red - all you need test is that they fall within the 1-36 boundaries)

On a final note - in the above i've replicated your setup using ; delimiter rather than , this is very unusual setup for Oz - so if you get an error revert to , rather than ;