Excel – Alternate Grid Background Color When Column Value Changes

conditional formattingmicrosoft-excel-2010

I need a formula for conditional formatting to make an Excel 2010 spreadsheet alternate highlighting when a single column changes. This question, "How can I alternate grid background color in excel when a value of a single column changes?", is exactly what I want to do, but the suggested solution didn't work for me–it just highlights the first row of each matching value.

I know how to use conditional formatting, but for the life of me I can't figure out or find any pointers on the net so far to make this work. Ideas?

Here is a link to a picture of how I want my spreadsheet to look when I'm done. Basically I want every other Disp Number value row to be highlighted. Seems like it would be a common thing to want to do, and I've seen this asked for in various places, but people struggle with making it work.

Picture of desired end result

Best Answer

This answer is copied straight from stackoverflow.com Alternating coloring groups of rows in Excel.

I use this formula to get the input for a conditional formatting:

=IF(B2=B1,E1,MOD(E1+1,2))    [content of cell E2]

Where column B contains the item that needs to be grouped and E is an auxiliary column. Every time that the upper cell (B1 on this case) is the same as the current one (B2), the upper row content from column E is returned. Otherwise, it will return that content plus 1 MOD 2 (that is, the outupt will be 0 or 1, depending on the value of the upper cell).

enter image description here

enter image description here

enter image description here

As an alternative to the MOD function, you could use 1 - E1. So full formula is =IF(B2=B1,E1,1-E1).

A pretty similar method is described in Color Banding Based On Content, where a downloadable example is included.

Related Question