Excel: conditional formatting for value clusters

Question

In an EXCEL 2010 spreadsheet, how can I create alternating fill colors for cluster 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 would like all 123 values ​​in A2: A4 to have the same fill color (say green), all 456 cells in A5: A6 the new fill color (say blue), and all 789 cells in A7: A10 back to the fill color used for 123 values ​​(green again).

This differs from many examples of duplicate values.

This is an exercise that I would like to repeat over and over for reports where the values ​​will change dynamically and cannot be expected.

The VBA solution will be great.

Thanks.

Answer 1

If you want to alternate colors, try the following:

Format the entire range in blue. Enter this formula in a secondary column, such as column B, starting at line 2 and copying down.

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

Then select the lines and add conditional formatting using this formula

 =$B2 

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

Answer 2

From what I understand, you can use the usual simple conditional formatting.

(Keep in mind that I translated from French so your menu could be a little different).

• Choose your details;
• On the Home tab, click Conditional formatting , and then highlight Color scales and select what suits you best. You can customize this if you want.

This will use the same color for each repeating number. This may not be acceptable if your range is large and you have close data (the color will be very similar, difficult to distinguish).

Answer 3

Thanks to @teylyn for directing me to solve this puzzle.

I am adding a little variation here by adding two levels of conditional formatting for color management and demonstrating how to make color shading span lines.

In this advanced solution, I added an extra Value column to satisfy the additional requirements of adding row-based fill.

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

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

To get colors for line coverage, change the Applies To text box to cover the entire range of values ​​from $ A $ 2: $ C $ 10.

This is a great solution. Now, to add a third color, or maybe a random color ... there is another day for this.

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

There is no image, but here is 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 is the formula for updating the values ​​in column C:

= IF (A2 = A1, C1, IF (C1 = 1.0.1))