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).

Answer 3

Thanks to @teylyn for steering me right on a solution to this puzzle.

I'm adding here a slight variation, adding two levels of conditional formatting to control color, and demonstrating how to get the color shading to span across the rows.

In this expanded solution, I've added an additional "Value" column just to satisfy the additional requirement of adding fill across the rows.

The "Helper" column works the same as @teylyn suggested, evaluating each cell value in column A to the cell value above. If this evaluates to true, the helper column uses the column C value in the previous row to set the current row column C value. If false, the current row column C value is set to 0.

Once these values are set, the conditional formatting can be done by selecting all values in column C (select C2, click ctrl-shft-down arrow), and then setting the first conditional rule to use green fill if $C2 evaluates to 1. Add a new rule, setting the fill color to blue if $C2 evaluates to 0.

To get the colors to span the rows, change the "Applies to" textbox to span the entire range of values from $A$2:$C$10.

It's a splendid solution. Now, to add a third color, or maybe a random color ... there's another day for that one.

Stack Overflow may not allow me to post an image showing the solution (sorry).

No image, but here's what the sheet values look like:

Row Value   Helper
123 ABC 1
123 DEF 1
123 GHI 1
456 JKL 0
456 MNO 0
789 PQR 1
789 STU 1
789 VWX 1
789 YZA 1

And here's the formula to update values in column C:

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