Locking cell color while allowing data changes in cell

[Barb]

In excel 2000, I created an attendance worksheet for my
classes.(Alphabetized names down left vertical column. Dates across
top of horizontal row.) I added a different color to all cells in
every other row to make for easier reading of each student's name and
absences. Every other row stays with a white background.

My question: I wondered if it was possible to lock row colors while
allowing data to change on top of them. If a new student is added to
my class in alphabetical order, the alternating color pattern is often
lost. It is a pain to rechange row and cell colors.
Any shortcut available? Any way to protect color rows but still be
able to change data?

 

[Gord Dibben]

Barb

Select the rows(not cells, rows) you wish to format.

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

Format>Pattern pick a color>OK your way out.

Deleting and/or inserting rows will not disrupt the alternate row shading.

Gord Dibben Excel MVP XL2002

 

[Jeff Donahue]

What I would do to insert a student after you have the
formatting the way you want it is this. Instead of
inserting a row to add a student, copy all the students
below where you want the new student and then do Edit-

Paste Special->Values to paste all the students down one

row.

What this will do is to copy all the data down but it will
leave the formatting. The only thing you may have to do
then is to format the extra row at the bottom unless you
have formatted the entire worksheet with the every other
row highlighting.

Hope this helps.

Jeff