Conditional Formatting VBA

[GordonLS]

Good morning, so I am trying to clean up the code for this. This code will fill rows with color based off alternating order numbers from Row 2 thru Row 500.


Rows("2:500").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(SUMPRODUCT(--($e$2:$e2<>$e$1:$e1)),2)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With



What I would like to do is incorporate the following so that the rows selected are only the rows that have data in Column A.
'Range("A1").Select
'Selection.End(xlDown).Select
'lastRow = ActiveCell.Row

But I need the alternating fill to work from row A1 through the "lastRow" and for as many columns that have data.

Just let me know if more information is needed.

Gordon

 

[Joe4]

Like this?

    Dim lr As Long
    lr = Cells(Rows.Count, "A").End(xlUp).Row

    Rows("2:" & lr).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=MOD(SUMPRODUCT(--($e$2:$e2<>$e$1:$e1)),2)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.14996795556505
    End With

 

[GordonLS]

thanks Joe, I will give this a shot.