Format even rows of a range and leave the odd rows alone

[Bagharmin]

I have a range (say, A10:E100) and I want to fill all the cells on even-numbered rows with a specific color. I know I can loop through each row in the range, check to see if it's an even-numbered row and, if so, fill it with the color I want. What I'd like to know is whether there is a simple way to fill all the cells on even-numbered rows at once (without looping). Something like With ActiveSheet.Range("A10:E100").Rows.Even.

Is this possible or will I be forced to loop through them?

 

[GlennUK]

Have you considered using Conditional Formatting with this? .. testing row number for even, like =ISEVEN(ROW())

 

[Bagharmin]

I acutally did consider that, but the range I'll be working with can change in size each time this particular report is run and I didn't think it would really work for me. I even used the macro recorder in an effort to see how I could code conditional formatting, but the results I got weren't particularly helpful. However, inserting conditional formatting into my code may be the way to go. I can probably get the initial formula test set up, but I'm unsure how to apply it to the range I want.

 

[GlennUK]

Yes, inserting conditional formatting in your code is the way to go. Use the formula that I gave, as is, on whatever range.

 

[Gerald Higgins]

As an aside, in Excel 2003, you can't actually use =ISEVEN(ROW()) within standard CF to colour alternate rows, although you MIGHT be able to use it in VBA, I haven't tried.
If you're doing it in standard worksheet CF, you can use something like

=MOD(ROW(),2)=0

 

[Bagharmin]

Thanks. I got it. I made some minor modifications (as I ended up having to check for one more condition), but it worked (with a little bit of help from the macro recorder). Here's the result:

Range("A10:J" & CliStop).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(NOT(A10="""")=TRUE,ISEVEN(ROW(A10))=TRUE)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.4
    End With
    Selection.FormatConditions(1).StopIfTrue = False

Thanks for letting me bounce this off you.