how do I shade alternating rows in spreadsheet

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

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

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

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.


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