Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
i looked. Be damned if i can figure it out - without resorting to writing a macro. –  Ian Boyd Feb 23 '11 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. –  Iszi Feb 23 '11 at 4:42
    
add comment

2 Answers

up vote 4 down vote accepted

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.
share|improve this answer
1  
This 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 Feb 27 '13 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/… –  Steve Jul 19 '13 at 14:30
add comment

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!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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