how do i lock in highlights for every other row in excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet and I have highlighted every other row. When I delete
or add a row, the highlighting sequence becomes messed up. I then have to go
back and manually highlight every other row. Is there a way to format the
spreadsheet so that when I add or delete a row, the spreadsheet automatically
keeps the "every other row highlighted" sequence in effect?
 
You can use conditional formatting to and specify the formula:

=MOD(ROW(),2)=0

You would then have the highlight color on all even numbered rows.
 
Select a gaggle of rows.

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

Pick a color and you're good to go.

Note: this will mess up if you are using a Filter.

For that you need a different Formula is:

=MOD(SUBTOTAL(3,$A$1:$A2),2)


Gord Dibben MS Excel MVP
 
Thanks for the help. I am selecting the rows I want but when I enter the
formula all I get is a "FALSE" in A1. What am I doing wrong?
 
Thanks, I still must be doing something wrong. I select the rows I want and
when I enter the formula all I get is a "false" in row 1. Do you know what I
am doing wrong?
 
You don't type the formula in the cell.

You select the cell(s) and then
Format|Conditional Formatting|Formula is
(Format is on the worksheet menu bar -- at the top.)
 
Thank you. That worked, every other row is highlighted however, when I
insert a new row the sequence gets out of whack in every row below the newly
inserted row (they all become highlighted). I am trying to format the
spreadsheet to automatically adjust the highlighted rows to every other one
when I add or delete an existing row. Is that possible?
 
Thanks to everyone for the help. Finally got it to work. I was working on
an existing spreadsheet with every other row already highlighted. Discovered
that I had to clear all highlights, then choose the cells again and apply the
formula. Once I cleared the original highlights and applied the formula, it
worked perfectly. Thanks again.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Change color of rows every time value in first column changes 2
how do I copy every 2nd row 5
How do I format every 140th row? 4
I cannot highlight a whole row or column 1
automatically highlighting every other row 2
conditional formating: ifs and highlighting rows 8
highlight from top to bottom of spreadsheet without scrolling? 4
Conditional Format - Highlighting the whole row 2

Back
Top