Excel: Conditional formatting for clusters of values

Question

In an EXCEL 2010 spreadsheet, how can I create alternating fill color for clustered cell values, changing when a new value cluster is reached?

Assume column A contains the following values:

VALUE

123

123

123

456

456

789

789

789

789

I'd like all of the 123 values in A2:A4 to have the same fill color (say, green), all of the 456 cells in A5:A6 a new fill color (say blue), and all of the 789 cells in A7:A10 back to the fill color used for the 123 values (green again).

This is different from the many duplicate value examples.

It's an exercise that I'd like to repeat over and over for reports where the values will change dynamically, and can't be anticipated.

A VBA solution would be fine.

Thanks.

Answer 1

If you want alternating colours, try this:

Format the whole range in blue. Enter this formula into a helper column, for example column B, starting in row 2 and copied down.

=IF(A2=A1,B1,IF(B1=1,0,1))

Then select the rows and add conditional formatting with this formula

=$B2

Select green as a cell fill. See screenshot. You can hide column B.

Answer 2

From what I understand, you can use regular plain simple conditional formatting.

(Keep in mind that I am translating from french so your menu may be a bit different).

1. Select your data;
2. On the Home tab click Conditional formatting and then highlight Color scales and select whichever suits you most. You can customize this if you want.

This will use the same color for each duplicate number. This may not be appropriate if your range is big and you have close data (color will be very similar, hard to differentiate).