row placement & alternating highlighting

[RSteph]

It's been a while since I've done much with Excel besides making quick &
dirty spreadsheets, so I'm a little out of practice.

I'm trying to make a spreadsheet that shows a list of what version of our
software each individual is on, to help with support questions based on
version. I have the names in a column on the right, and the version #'s
listed above, with all the data listed. Now I'm trying to do two things to my
sheet to make it more readable.

1) I'd like to shade the rows in an alternating pattern (even # rows grey,
odd # rows white) to make it more readable.

2) I'd like to make the top row (the header row) appear at the top of the
page at all times. I know how to make it do this in printing, put it at the
top of each page, but I'm not sure how to get it to stay there on the actual
spreadsheet file.

I could probably do some of this with a macro if I sat down and through
through the steps, but I was curious if there was an easier way of doing
this? I was going to use conditional formatting for the highlighting, but
there is no even/odd option. And I thought there was a quick way to place a
row always at the top of the page, but I can't remember what it is.

Any help would be greatly appreciated. Thank you.

 

[John Bundy]

1) Highlight the area you want to format. Go to Format->Conditional
Formatting. Change the drop-down to Formula Is, and type the following in the
space provided:
=Row()=Even(Row())
Next select Format button, click the pattern tab and select the color you
want, hit OK twice and you're done.

2) Select the entire second row by clicking the 2 on the left, go to
Window->Freeze panes.

Thats it

 

[Jarek Kujawa]

1) conditional formatting:
Formula Is
=MOD(ROW();2)=0
and select grey interior, then copy and paste special as formats
this will not color the whole rows, only specific cells

2) select a cell somwehere in 2nd row and go to Window->Freeze Panes

 

[John C]

To keep some rows always on screen, click in the cell just below what you
always want frozen, then go to menu Window-->Freeze Panes.

To do the every other row, I did the following:
Highlight Selection where you want it to appear (I would not recommend
conditional formatting the entire spreadsheet).
Clicked in cell A1, went to menu Format--> Conditional Formatting
Condition 1:
Formula is:
=Row()/2=INT(ROW()/2)

 

[RSteph]

Thank you, all three of you, for the quick responses to my question. Your
information was very helpful.

 

[Gord Dibben]

John

A caveat here..................

Your formula will not survive through filtering

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


Gord Dibben MS Excel MVP