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

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

=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:

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.

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.

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.

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.

Hi Gordon,
Thanks for that. I was trying it on a data-less sheet.
Regards - 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.