Conditional Formatting Shading by Group When Filtered

[exs120]

Hello,

I am using Excel 2010 on XP. I would like to apply conditional formatting to shade every group in alternating color. For example, I have a list of US states. I have a formula already to produce this:

Current Conditional Formatting Formula: =MOD(SUMPRODUCT(--($a$1:$a1<>$a$2:$a2)),2)

STATES (unfiltered)

AL
AL
AK
AK
AK
AR
AR
AZ
AZ
AZ
…


When I apply a filter, the rows remain shaded as they were originally:

STATES (Filter OUT AK)

AL
AL
AR
AR
AZ
AZ
AZ
…

I want the conditional format to change when I have filtered out items (DESIRED RESULT):

STATES (Filter OUT AK)

AL
AL
AR
AR
AZ
AZ
AZ
…

I would assume SUBTOTAL(3,...) would need to be incorported into the conditional formatting formula above, but I do not know how.

Thanks for your help!

[Admin]

Hi

Try

=MOD(SUMPRODUCT(SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$A 2)-ROW($A$2)+1,,1)),--($A$1:$A1<>$A$2:$A2)),2)

[exs120]

It works without the +1 =MOD(SUMPRODUCT(SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$A 2)-ROW($A$2),,1)),--($A$1:$A1<>$A$2:$A2)),2)

Thanks for your help!