How can I alternate grid background color in excel when a value of a single column changes?
Such as this:
I would want to do this with conditional formatting, but I can't figure out how to.
3
-
i looked. Be damned if i can figure it out - without resorting to writing a macro.– Ian BoydFeb 23, 2011 at 2:57
-
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.– IsziFeb 23, 2011 at 4:42
-
possible duplicate of Alternate grid background color in excel when a value of a single column changes?– nixdaOct 6, 2013 at 11:14
Add a comment
|
2 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.
-
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.– TriynkoFeb 27, 2013 at 1:24
-
The answer works, but as @Triynko said, it is very resource intensive. Perhaps this solution is better from a performance perspective: superuser.com/questions/553899/…– SteveJul 19, 2013 at 14:30
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!
