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

[Bob Phillips]

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)

 

[Dave]

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

 

[Gord Dibben]

=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

 

[Dave]

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.

 

[Dave]

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.

 

[Gord Dibben]

Select a great whack of rows.

Format>CF>Formula is: =MOD(SUBTOTAL(3,$A1:$A$2),2)=0

Format>Pattern 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

 

[Bob Phillips]

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)

 

[Dave]

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

 

[Dave]

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.