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

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

QUESTION: Thanks!  That worked . . . but of course it leads to the next predicament:  Now that all records of a common denominator (Column A values)are shaded alike, I need to format the single record among each group (shaded or not) which has the highest value, i.e.  Records 1~5 are all shaded and have the following respective values (in Column B) 2, 4, 6, 3, and 1.  Cell B3 is in bold font because of the five records it has the highest 'B' value.  Please/Thanks!

Answer
You need another helper column. If I is available, then in I2 enter this formula using ctrl+shift+enter:
=MAX(IF($A$2:$A$100=A2,$B$2:$B$100))
then fill down.
Select column B, use conditional formatting, use formula =B1=I1, give it a bold font, and you're done.