Excel: Conditional formatting for clusters of values
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
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.
