Coloring Alternate Rows in Excel 2003

[pkelley]

I'm dealing with a spreadsheet with 134,000+ rows and it's getting very hard to read across w/o jumping from line to line. Is there an easy way to color alternating rows with a different color (similar to an accounting ledger)?

 

[mwrohr]

Yeah, could be a fairly straightforward macro really:

Dim r as range, r2 as range

Set r = Sheet1.range("A1")
Set r2 = Sheet1.range("A65000").end(xlup)

While r.row <= r2.row
r.entirerow.interior.colorindex = 7
Set r = r.offset(2,0)
Wend


you'll need to validate for how many rows in your version of Excel; I thought 2003 only allowed 65556 rows? Also you'll need to figure out the color you want to set the row to

 

[mikerickson]

you can use conditional formatting with a formula like =(MOD(ROW(),2)=1)

My preference is to hightlight every third cell with =(MOD(ROW(),3)=1)

 

[pkelley]

Thanks for your reply. I'll give it a try. BTW, I got my numbers screwed up. The spreadsheet has 34,000 + rows; not 134,000. That's still a lot of rows for my 66 year old eyes.

 

[mwrohr]

No worries, my 32 year old eyes can barely handle 100 rows at this point!