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

 

[fairwinds]

Hi,
If you use conditional formatting with Formula is:
=EVEN((SUBTOTAL(3,$A$1:A1)))=SUBTOTAL(3,$A$1:A1)
It will colour every second even if you filter.

 

[Andrew Poulsom]

Hi,
If you use conditional formatting with Formula is:
=EVEN((SUBTOTAL(3,$A$1:A1)))=SUBTOTAL(3,$A$1:A1)
It will colour every second even if you filter.

That's excellent!

 

[inarbeth]

That's excellent! [/quote]

I agree. I have wanted to do this for years. Wonderful, fairwinds

 

[inarbeth]

On further checking, the formula works on a single column but when applied to a range across columns (e.g. A1:F10) it gives a part-checkerboard effect. Is there a way of modifying the formula to prevent this?

 

[Andrew Poulsom]

Just take out the absolute reference to column A:

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

 

[Smitty]

In addition to Fairwinds awesome approach, here's a VBA method that works well.

http://support.microsoft.com/default.aspx?scid=kb;en-us;213616&Product=xlw2K

Smitty

 

[parry]

I think this works too using conditional formatting and seemed to work with filtering on a quick test.

to colour odd row first
=MOD(ROW(),2)=0

to colour even row first
=MOD(ROW(),2)=1

EDIT
I take that back, it failed on another test so as you were. Seems to only work with filtering off.

 

[inarbeth]

Just take out the absolute reference to column A:

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

Hi, Andrew, I tried that but it only works if all cells contain something. A single empty cell reverses the row in question.

 

[fairwinds]

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