Shade alternate VISIBLE rows only in EXCEL

[MJL]

I have been trying for a while to crack this problem and so far I have been able to shade alternate rows using conditional formatting. However, my spreadsheet has a filter attached to it and I only need the alternate shading to be applied to the VISIBLE rows (after the filter has been applied).
The current result gives me a barcode affect where shading of the VISIBLE rows are not alternate and appear random.

I hope I have explained myself well enough for you to understand my problem.

I appreciate any help

Thank you

 

[inarbeth]

Somewhere in your filter range you need a column with entries in all rows. Then you let your formula refere to that column.

If column A has items in every row the formula becomes
=EVEN((SUBTOTAL(3,$A$1:$A1)))=SUBTOTAL(3,$A$1:$A1)

note the $A1 reference.

 

[VBPari]

Hi Andrew,

Your code has helped me a lot.
Thanks

Just take out the absolute reference to column A:

=EVEN((SUBTOTAL(3,A$1:A1)))=SUBTOTAL(3,A$1:A1)

 

[alex7965]

This works like a charm! I would love to understand better what it is that the formula does. And how do you end up with two "="s, what does that mean/do?

Just take out the absolute reference to column A:

=EVEN((SUBTOTAL(3,A$1:A1)))=SUBTOTAL(3,A$1:A1)

 

[kev6264]

This works like a charm! I would love to understand better what it is that the formula does. And how do you end up with two "="s, what does that mean/do?

=EVEN((SUBTOTAL(3,A$1:A1)))=SUBTOTAL(3,A$1:A1)

EVEN - Rounds a positive number up and negative number down to the nearest even integer
SUBTOTAL option 3 - COUNTA function... it is counting filled in cells (A1:A1)... When copied down the reference changes to A$1:A2 and A$1:A3....etc...

=

SUBTOTAL option 3 - Same thing as above... totals the number of filled in cells with COUNTA

So this formula would calculate to FALSE... 2 = 1
=EVEN((SUBTOTAL(3,A$1:A1))) = 2
=SUBTOTAL(3,A$1:A1) = 1

 

[jsdfjksd83]

How could I adapt this formula to change the row color whenever there's a group (value) change, e.g. in column A?