how do I shade alternating rows in spreadsheet

[JasonH]

I want to shade every other line in Excel spreadsheet but if I go back and
insert an additional line it duplicates the color of previous line. I would
like it to switch to alternating colors as if I had not inserted an
additional line.

 

[Bob Phillips]

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

 

[JLatham]

Select all of the rows you need to set up, use
Format --> Cells -->Conditional Formatting
Change from "Cell Value Is" to "Formula Is"
and enter
=MOD(ROW(),2)=0
(yes, just like that with both = symbols in it)
and then set the format that you want even numbered rows to have (on the
patterns tab), [OK], [OK] and poof! you're done.

 

[Steven]

Select the area that you want to shade (if you want the entire sheet
CTRL A)

In conditional formatting
Choose "Formula Is" =MOD(ROW(),2)

 

[Gord Dibben]

If you will be doing any filtering on the rows you will want to use

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

=MOD(ROW(),2)=0 won't retain banding when data is filtered.


Gord Dibben MS Excel MVP

Select all of the rows you need to set up, use
Format --> Cells -->Conditional Formatting
Change from "Cell Value Is" to "Formula Is"
and enter
=MOD(ROW(),2)=0
(yes, just like that with both = symbols in it)
and then set the format that you want even numbered rows to have (on the
patterns tab), [OK], [OK] and poof! you're done.

I want to shade every other line in Excel spreadsheet but if I go back and
insert an additional line it duplicates the color of previous line. I would
like it to switch to alternating colors as if I had not inserted an
additional line.

 

[winnersinclair]

I want to shade every other line in Excel spreadsheet but if I go back and
insert an additional line it duplicates the color of previous line. I would
like it to switch to alternating colors as if I had not inserted an
additional line.

Guys there is a much MUCH easier way to do with without special code or scripts or anything.

-Simply paint the two lines the colors that you want repeated
-Highlight the area or complete rows you want copied
-Click the "Paint Formatting" tool
-Paint that $%iT down your document (Click and drag as desired)
-Laugh at all the code you didn't have to write.
-Profit

 

[GS]

Guys there is a much MUCH easier way to do with without special code
or scripts or anything.

-Simply paint the two lines the colors that you want repeated
-Highlight the area or complete rows you want copied
-Click the "Paint Formatting" tool
-Paint that $%iT down your document (Click and drag as desired)
-Laugh at all the code you didn't have to write.
-Profit

What 'code' are you talking about?
Why go to all that trouble when a simple CF formula will do?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Christopher Stevenson]

Painting doesn't seem any easier, but in any case it won't survive a row deletion.