Shading groups of rows based on changing text values

Question

I would like to alternate the shading of a group of rows each time the text value of a column changes. So all rows with "abc" in column B, will be shaded blue, when the value of column B changes to "def", the rows will be shaded green. The the next time the value of column B changes, the next group of rows will be shaded blue, etc. Seems like it should be easy, but I have not figured it out! I am on a Mac using excel 2008.

Answer 1

I think that you are looking for something similar to this:

Notice that you need to:

• Use a helper column (which could be hidden),
• Populate the first cell of the helper column with the number 1,
• Populate the next cell with the formula =IF(B3=B2,E2,E2+1), and copy down,
• Choose the "Use a formula to determine which cells to format" option in Conditional Formatting,
• Use the Rules as shown in the picture,
• Apply both Rules as shown in the picture.

Answer 2

If you don't want to use a helper column:

You can use the below in your conditional formatting. Replace $B with the column that has your preferred groups. I have an extra check at the beginning to make sure it's not blank, that way your data can grow.

=AND($A2<>"",ISEVEN(COUNTA(UNIQUE($B$2:$B2))))

Should keep things a little cleaner without the helper column!