How do I set up alternate row shading with Excel 2003?
 

How do I set up alternate row shading with Excel 2003?
 

See http://www.xldynamic.com/source/xld.CF.html#rows

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"msk1944" wrote in message
...

How do I set up alternate row shading with Excel 2003?
 

Hi,
Select all data you want to alternately shade.
In conditional Format, Formula is:
=MOD(ROW(),2)=0
Set desired shading format.
OK
Regards - Dave

How do I set up alternate row shading with Excel 2003?
 

=MOD(SUBTOTAL(3,$A1:$A$2),2)=0

This one will retain the banding when you filter the data.

=MOD(ROW(),2)=0 will not retain the banding through filtering.


Gord Dibben MS Excel MVP

On Tue, 3 Jun 2008 15:36:02 -0700, msk1944
wrote:

How do I set up alternate row shading with Excel 2003?
 

Hi Gordon,
I didn't know about using subtotal to maintain banding. (Does that mean
boarders?) I usually just include gray boarders in the conditional format.
I tried your formula, but no success - it just colored all cells with the
selected format. What am I doing wrong?
Regards - Dave.

How do I set up alternate row shading with Excel 2003?
 

Oh yeah, now I get it. It still maintains its every-other-row shading, even
after filtering. That would be very cool. Can you please help me make it work?
Dave.

"Dave" wrote:

Hi Gordon,
I didn't know about using subtotal to maintain banding. (Does that mean
boarders?) I usually just include gray boarders in the conditional format.
I tried your formula, but no success - it just colored all cells with the
selected format. What am I doing wrong?
Regards - Dave.

How do I set up alternate row shading with Excel 2003?
 

Select a great whack of rows.

FormatCFFormula is: =MOD(SUBTOTAL(3,$A1:$A$2),2)=0

FormatPattern to a nice light gray color.

Nothing to do with borders.

Note the placement of the $ signs.........you want $A1 to be column absolute and
row relative but $A$2 to fix both row and column at absolute.

The SUBTOTAL(3,range) function is same as COUNTA on visible cells that remain
after filtering.

COUNTA counts cells that are not empty.


Gord

On Tue, 3 Jun 2008 17:41:01 -0700, Dave wrote:

Oh yeah, now I get it. It still maintains its every-other-row shading, even
after filtering. That would be very cool. Can you please help me make it work?
Dave.

"Dave" wrote:

Hi Gordon,
I didn't know about using subtotal to maintain banding. (Does that mean
boarders?) I usually just include gray boarders in the conditional format.
I tried your formula, but no success - it just colored all cells with the
selected format. What am I doing wrong?
Regards - Dave.

How do I set up alternate row shading with Excel 2003?
 

It works okay if there is data in A1:An, but gives a horrible coloured block
if they are empty.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Select a great whack of rows.

FormatCFFormula is: =MOD(SUBTOTAL(3,$A1:$A$2),2)=0

FormatPattern to a nice light gray color.

Nothing to do with borders.

Note the placement of the $ signs.........you want $A1 to be column
absolute and
row relative but $A$2 to fix both row and column at absolute.

The SUBTOTAL(3,range) function is same as COUNTA on visible cells that
remain
after filtering.

COUNTA counts cells that are not empty.


Gord

On Tue, 3 Jun 2008 17:41:01 -0700, Dave
wrote:

Oh yeah, now I get it. It still maintains its every-other-row shading,
even
after filtering. That would be very cool. Can you please help me make it
work?
Dave.

"Dave" wrote:

Hi Gordon,
I didn't know about using subtotal to maintain banding. (Does that mean
boarders?) I usually just include gray boarders in the conditional
format.
I tried your formula, but no success - it just colored all cells with
the
selected format. What am I doing wrong?
Regards - Dave.

How do I set up alternate row shading with Excel 2003?
 

Hi Gordon,
Thanks for that. I was trying it on a data-less sheet.
Regards - Dave.

How do I set up alternate row shading with Excel 2003?
 

Hi Bob,
Thanks for that. I have no idea why it works. If I did, I guess I'd have
known it needed some data. Care to enlighten me?
Regards - Dave.