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.

Alternate Row Shading (Visible Rows Only)
 

Have a look here

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

Mike

"Forgone" wrote:

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.

Alternate Row Shading (Visible Rows Only)
 

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

"Forgone" wrote in message
...
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.

Alternate Row Shading (Visible Rows Only)
 

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.
--
Regards
Roger Govier

"Ashish Mathur" wrote in message
...
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

"Forgone" wrote in message
...
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.

Alternate Row Shading (Visible Rows Only)
 

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

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
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.
--
Regards
Roger Govier

"Ashish Mathur" wrote in message
...
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

"Forgone" wrote in message
...
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.

Alternate Row Shading (Visible Rows Only)
 

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

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



On Sep 16, 4:17*pm, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote:
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.
--
Regards
Roger Govier

Alternate Row Shading (Visible Rows Only)
 

102 is COUNT which will count numerics only.

Is that what you want?

103 counts everything.


Gord Dibben MS Excel MVP


On Tue, 16 Sep 2008 18:44:57 -0700 (PDT), Forgone
wrote:

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

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



On Sep 16, 4:17*pm, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote:
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.
--
Regards
Roger Govier

Alternate Row Shading (Visible Rows Only)
 

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!


On Sep 18, 5:24*am, Gord Dibben <gorddibbATshawDOTca wrote:
102 is COUNT which will count numerics only.

Is that what you want?

103 counts everything.

Gord Dibben *MS Excel MVP

Alternate Row Shading (Visible Rows Only)
 

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.

--
Regards
Roger Govier

"Forgone" wrote in message
...
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!


On Sep 18, 5:24 am, Gord Dibben <gorddibbATshawDOTca wrote:
102 is COUNT which will count numerics only.

Is that what you want?

103 counts everything.

Gord Dibben MS Excel MVP

Alternate Row Shading (Visible Rows Only)
 

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