shading alternate rows, but losing format when one row is deleted

[Candice]

I have my sheet rows shaded alternately gray/white, but when I delete a row,
the shading does not update, I have to re-format manually....
I wrote a macro: select all > autoformat >
and that works, but I am wondering if there is a setting that will just
auto adjust the row shading after I delete or ad a row without having
to run the macro or re-auto format?
Any suggestions will be greatly appreciated.
Thanks, Candice

 

[Gord Dibben]

Candice

Select all rows.

Format>Conditional Formatting>Formula is: =MOD(ROW(),2)=1

Pick a pattern from Format and OK your way out.


Gord Dibben MS Excel MVP

 

[Ragdyer]

You can use "Conditional Formatting", which will self-adjust after rows are
inserted or deleted.

Select the range (or all),
Then
<Format> <Conditional Format>
Select "Formula Is" in the first box drop-down,
Enter this formula in the next box:

=MOD(ROW(),2)=0

Click "Format" and choose a 'Pattern' color to your liking (Gray),
Then <OK> <OK>

Now, all *even* rows are colored.

To have *odd* rows colored, change formula to:

=MOD(ROW(),2)

 

[Chip Pearson]

See www.cpearson.com/excel/banding.htm for information about how
to do this with Conditional Formatting.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Candice]

THANK YOU!!!!

Candice

Select all rows.

Format>Conditional Formatting>Formula is: =MOD(ROW(),2)=1

Pick a pattern from Format and OK your way out.


Gord Dibben MS Excel MVP

 

[Candice]

THANK YOU TO ALL.
You guys are really terrific!

 

[waddsn1060]

I am trying to do the same thing but when I enter either:

=MOD(ROW(),2)=0 or =MOD(ROW()-Rw,N*2)+1>N

Into the conditional formatting box, nothing happens. I can go back
into conditional formatting and it will say Formula is =MOD(ROW(),2)=0
or =MOD(ROW()-Rw,N*2)+1>N with my settings.

Help!

 

[Ragdyer]

Are you selecting a *range* BEFORE you click into "Conditional Formatting"?

Are you choosing a *pattern* color AFTER you enter your formula?

 

[waddsn1060]

I found the problem, I was selecting the wrong area. Thanks!