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

On Sat, 22 Apr 2006 16:07:01 -0700, Candice <[email protected]>
wrote:

>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

[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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Candice" <[email protected]> wrote in message
news:[email protected]...
> 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

[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" <[email protected]> wrote in message
news:[email protected]...
>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

[Candice]

THANK YOU!!!!

"Gord Dibben" wrote:

> 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
>
> On Sat, 22 Apr 2006 16:07:01 -0700, Candice <[email protected]>
> wrote:
>
> >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
>
>

[Candice]

THANK YOU TO ALL.
You guys are really terrific!

"Candice" wrote:

> 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

[[email protected]]

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?
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
<[email protected]> wrote in message
news:[email protected]...
> 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!
>

[[email protected]]

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