Alternate row colouring that works with filtering?

[StargateFanFromWork]

I just noticed something on a new workbook I've made. The regular alternate
row colouring using the format "=MOD(ROW(),2)=0" doesn't accommodate
filtering. The rows don't come out alternately coloured, sometimes several
rows have the same yellow or white, etc. I'm guessing that they're actually
coming out based on their original positions in the workbook.

Is there anything that produces alternate row colouring when filtering
columns that still works with unfiltered rows?

Thanks.

 

[Debra Dalgleish]

You can use a subtotal in the formula, e.g.:

=MOD(SUBTOTAL(3,$A$1:$A2),2)

There's an example here:

http://www.contextures.com/xlCondFormat03.html#Filter