Shading groups of rows based on changing text values

2

1

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.

Linda Topper

Posted 2015-07-08T20:52:06.810

Reputation: 21

Thank you, but that is not exactly what I am trying to do. I have sorted by the value in column B. So all rows with "abc".are grouped together followed by all rows with "def", then "xyz". So I would just like to easily be able to see the grouping of rows where the values in column be are the same and, using just two colors, alternately delineate the change. So if I have a list of names, address and phone numbers and sort them by everyone that lives in the same city, then I can easily see the groups when the city changes. – Linda Topper – 2015-07-09T14:23:04.007

So regardless of how many cities, you want only two colors, and you want them to alternate whenever the city changes. Am I understanding that correctly? – Clif – 2015-07-10T23:03:35.647

I have updated my answer. – Clif – 2015-07-11T00:05:44.463

Answers

4

I think that you are looking for something similar to this: enter image description here

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.

Clif

Posted 2015-07-08T20:52:06.810

Reputation: 396

Asked: 2015-07-08T20:52:06.810

Viewed: 9 504 times

Active: 2015-07-11T00:03:23.227