How can I alternate grid background color in excel when a value of a single column changes?

Such as this:

enter image description here

I would want to do this with conditional formatting, but I can't figure out how to.

Matt

Posted 2011-02-23T02:12:12.520

Reputation: 5 109

i looked. Be damned if i can figure it out - without resorting to writing a macro. – Ian Boyd – 2011-02-23T02:57:41.280

Interesting idea. I'm sure I could probably figure out a way, but it would be a total kludge. Will be interesting to see if anyone does come up with something. – Iszi – 2011-02-23T04:42:10.487

possible duplicate of Alternate grid background color in excel when a value of a single column changes?

– nixda – 2013-10-06T11:14:50.773

Answers

Conditional format formula

=ISODD(SUM(IF(FREQUENCY(MATCH($B$2:$B2,$B$2:$B2,0),MATCH($B$2:$B2,$B$2:$B2,0))>0,1)))

Assumptions:

Range to apply formatting applies from row 2 down
Apply above conditional format to all cells in row 2 that require shading
paint cell format from row 2 to all rows that require format
that the value to check for change is in column B.

chris neilsen

Posted 2011-02-23T02:12:12.520

Reputation: 4 005

1This makes Excel grind to a halt on my machine, FYI. Especially when scrolling down through the conditionally formatted rows. Is there any way to copy the dynamic formatting and permanently apply it so that Excel doesn't lag terribly? There's a much more efficient way to do this with a hidden column. – Triynko – 2013-02-27T01:24:27.030

The answer works, but as @Triynko said, it is very resource intensive. Perhaps this solution is better from a performance perspective: http://superuser.com/questions/553899/alternate-grid-background-color-in-excel-when-a-value-of-a-single-column-changes

– Steve – 2013-07-19T14:30:38.873

The ISODD method kept on crashing my excel (30,000 records), so I did it another way. I ran a counter next to the variable I wanted to cond format on (so in the OP example, A=1, B=2 (each time col2 is different from one row above, add one). Then I used added a column with the formula =MOD(col2,2). Which gives an alternating 1,0,1,0 (all As 1, all Bs 0, all Cs 1). Then conditionally formatted on that column. Worked!

user261194

Posted 2011-02-23T02:12:12.520

Reputation: 11