Expert: Bob Umlas - 7/10/2009

Question
QUESTION: I can't seem to find this question/answer and this is my first visit so will ask it:

I want to shade the records of a worksheet (Claims & Dollars) alternating between No fill and filled (The color isn't important.) based on a given column's contents on that same record.  The first record (header) is frozen.  The Autofilter function is on.  Example:  Say I'm using Column A values to trigger the alternate shading.  A2=100,  A3=100, A4=101, A5=102.  Records 2 & 3 would be shaded, record 4 wouldn't and record 5 would, et cetera.  It doesn't matter whether the first record begins with shaded or No fill, to me.

ANSWER: If it weren't a filtered list, it'd be pretty straightforward, but since it's filtered, and you may have A2=100 rows 3:6 filtered, A7=100, A8=101, A9=101, rows 10:30 filtered, A31=400, you'd need rows 2 & 8 to be shaded, 8 &9 not shaded, 31 shaded. This is not at all trivial and would require vba code, not conditional formatting. It would take a fair amount of time to come up with something that'd work. Sorry

---------- FOLLOW-UP ----------

QUESTION: So, nix the filtered aspect.  That make it easy enough?

Answer
You need a helper column. Say you have col H available. Enter this in H2:
=IF(A2=A1,IF(H1=1,1,0),IF(H1=1,0,1))
fill down as far as necessary (same # of entries as in A)
Select everything, use conditional formatting, change "cell value is" to "formula is", enter this formula:
=$H1=1, click the Format button, pick a color.
You can hide column H

HTH