Alternate Row Shading (Visible Rows Only)



Quick question..... hopefully it is possible.

To have alternate row shading in XL2003 and earlier the conditional
formatting formula would be =MOD(ROW(),2)
This works find until I start hiding rows.....

Is there a way to have alternate row shading on visible rows?

This is so that I can retain the alternate row shading even if I start
to hide rows.

Ashish Mathur


Assume your data is arranged like this in range A2:B10.

Name Amount
A 10
S 12
D 14
F 16
G 18
T 20
Y 22
U 24

Now in C2, enter the following formula =SUBTOTAL(103,$B$3:B3). Copy this
down till C10. Now while in cell B3, enter the following formula in Format
Conditional formatting =MOD($C3,2)=0. Select the format desired. Now
copy this conditional format down.

Try hiding rows now. You will mow notice that every even numbered row which
is visible, will be in the desired format.

Please let me know how this works for you. Also, please note that this
method will only work in Excel 203 and above. SUMPRODUCT(100 series ....)
is only available in Excel 2003 and above.


Ashsih Mathur
Microsoft Excel MVP

Roger Govier

Hi Ashish

I don't think this works in the manner required
If you don't bother with formulae in column C, but just set the Conditional
Formatting formula to
then it will work.

Ashish Mathur

Dear Roger,

I guess both of us are saying the same thing. It's just that I used spare
column which could have been avoided.


Ashsih Mathur
Microsoft Excel MVP


As I'm applying it to a range of cells, I made a slight


Gord Dibben

102 is COUNT which will count numerics only.

Is that what you want?

103 counts everything.

Gord Dibben MS Excel MVP


Typing error.......

It's =MOD(SUBTOTAL(103,$B$2:$B2),2)=0

I had to put a dollar sign in front of the second B and it works a

Roger Govier

Only just seen your responses in this thread.
My apologies for omitting the $ on the second occurrence of B.
Glad you got that worked out for yourself.


Because I've got multiple ranges (financial report) with a series of
spaces and totals in between each "category" I've had to enter the
formula into each group manually as the "copy > paste special >
formats" or using the format painter doesn't help out as much because
of the $A$1 reference. Oh well...... it is a tedious job....

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question