Alternate Row Shading (Visible Rows Only)

[Forgone]

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.

 

[Mike H]

Have a look here

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

Mike

 

[Ashish Mathur]

Hi,

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.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[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
=MOD(SUBTOTAL(103,$B$2:B2),2)=0
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.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Forgone]

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

=MOD(SUBTOTAL(102,$B$2:$B2),2)=0

 

[Gord Dibben]

102 is COUNT which will count numerics only.

Is that what you want?

103 counts everything.


Gord Dibben MS Excel MVP

 

[Forgone]

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
treat!

 

[Roger Govier]

Hi
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.

 

[Forgone]

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....