EASY - Formatting Odd Numbered Rows

jrnyman · Apr 18th, 2002, 04:03 AM

I'm looking for the code to shade the interior of each odd numbered row to colorindex=15. Right now I go line by line with a manual counter that changes from 0 to 1 or 1 to 0 at the end of each run of the loop. There's got to be a more efficient way to do this. Any suggestions?

Tom Urtis · Apr 18th, 2002, 04:28 AM

(1) Click on Format > Conditional Formatting.
(2) In the first drop down box, select "Formula Is"
(3) In the field at the right of that, enter
=MOD(ROW(),2)=1
(4) Click the Format button, then Patterns tab.
(5) Color index 15 is light gray, so choose that and hit OK, OK.

nisht · Apr 18th, 2002, 04:32 AM

Sub colorevenrows()

Do While Not IsEmpty(ActiveCell)
If ActiveCell.Row Mod 2 = 0 Then
' for odd
' if activecell.row mod 2 <> 0 then
ActiveCell.EntireRow.Interior.ColorIndex = 15
End If
ActiveCell.Offset(1, 0).Select
Loop

End Sub

nishith desai
http://www.pexcel.com

[ This Message was edited by: nisht on 2002-04-18 03:33 ]

Tom Urtis · Apr 18th, 2002, 05:02 AM

jrnyman --

Thinking about your question and my response, you asked for code but I gave you a non-code answer because you also asked for efficiency. Loops tend to be inefficient, but so might a large range that is conditionally formatted. There're pros and cons with either approach; it depends on your preference and spreadsheet design.

jrnyman · Apr 18th, 2002, 05:19 AM

Thanks everyone for the help.

For anyone who thinks VBA is just like English chew on this:

Do While Not IsEmpty(ActiveCell)

translates to ENGLISH as..

Do (below) while active cell is not empty

Must have been too easy to do it that way....

Apr 18th, 2002, 04:28 AM	#2
Tom Urtis MrExcel MVP Join Date: Feb 2002 Location: San Francisco, California USA Posts: 10,387	(1) Click on Format > Conditional Formatting. (2) In the first drop down box, select "Formula Is" (3) In the field at the right of that, enter =MOD(ROW(),2)=1 (4) Click the Format button, then Patterns tab. (5) Color index 15 is light gray, so choose that and hit OK, OK. __________________ Tom Urtis Microsoft MVP - Excel

Apr 18th, 2002, 05:02 AM	#4
Tom Urtis MrExcel MVP Join Date: Feb 2002 Location: San Francisco, California USA Posts: 10,387	jrnyman -- Thinking about your question and my response, you asked for code but I gave you a non-code answer because you also asked for efficiency. Loops tend to be inefficient, but so might a large range that is conditionally formatted. There're pros and cons with either approach; it depends on your preference and spreadsheet design. __________________ Tom Urtis Microsoft MVP - Excel

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 18th, 2002, 04:03 AM	#1
jrnyman Board Regular Join Date: Mar 2002 Location: Boston, MA Posts: 105	I'm looking for the code to shade the interior of each odd numbered row to colorindex=15. Right now I go line by line with a manual counter that changes from 0 to 1 or 1 to 0 at the end of each run of the loop. There's got to be a more efficient way to do this. Any suggestions?

Apr 18th, 2002, 04:32 AM	#3
nisht Board Regular Join Date: Feb 2002 Location: Ahmedabad Gujarat Posts: 303	Sub colorevenrows() Do While Not IsEmpty(ActiveCell) If ActiveCell.Row Mod 2 = 0 Then ' for odd ' if activecell.row mod 2 <> 0 then ActiveCell.EntireRow.Interior.ColorIndex = 15 End If ActiveCell.Offset(1, 0).Select Loop End Sub nishith desai http://www.pexcel.com [ This Message was edited by: nisht on 2002-04-18 03:33 ]

Apr 18th, 2002, 05:19 AM	#5
jrnyman Board Regular Join Date: Mar 2002 Location: Boston, MA Posts: 105	Thanks everyone for the help. For anyone who thinks VBA is just like English chew on this: Do While Not IsEmpty(ActiveCell) translates to ENGLISH as.. Do (below) while active cell is not empty Must have been too easy to do it that way....