how do I shade alternating rows in spreadsheet

  • Thread starter Thread starter JasonH
  • Start date Start date
J

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


JasonH said:
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.
Click to expand...
Click to expand...
 
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.
Click to expand...

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
 
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
Click to expand...

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
 
Painting doesn't seem any easier, but in any case it won't survive a row deletion.
 
Back
Top