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.

  • 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 Jul 9 '15 at 14:23
  • 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 Jul 10 '15 at 23:03
  • I have updated my answer. – Clif Jul 11 '15 at 0:05

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.
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.