Excel Makro : apply to all rows in cell Columns("A:A").Select, its offset is wrong by 1

Hi, I have the following 2 columns in an excel sheet

Date	                Time
12/12/2011	9:06:45
12/12/2011	9:06:45
12/11/2011	10:49:10
12/13/2011	10:54:21
12/14/2011	23:46:10
12/15/2011	12:10:55
12/16/2011	12:13:38
12/17/2011	12:22:13
12/18/2011	13:07:47
12/1/2011	19:25:50

Open in new window


code is simple, highlight if Column A has a weekend date in it

    Cells.FormatConditions.Delete
    
    Columns("A:A").Select
 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=WEEKDAY(A2,2)>5"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    

Open in new window


now the problem, it currently selects 1 row above the correct row
e.g.
12/12/2011       
12/12/2011       Highlighted, but it should highlight next row as the 12th is a monday
12/11/2011       
12/13/2011       
12/14/2011       
12/15/2011       
12/16/2011         Highlighted, , but it should highlight next row
12/17/2011        Highlighted correctly
12/18/2011        Not highlighted, but it should be
12/1/2011       

also, how can i stop highlighting any rows that have no values please? you will
see that the cells with no data (at the bottom) are all selected

thanks
Test-Date-Selection.xlsx
jxhardingAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
Try this.
Dim rng As Range

    Set rng = Range("A2", Range("A" & Rows.Count).End(xlUp))

    With rng
        .FormatConditions.Delete


        .FormatConditions.Add Type:=xlExpression, Formula1:= _
                              "=WEEKDAY(A2,2)>5"
        .FormatConditions(1).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent6
            .TintAndShade = 0
        End With

        .FormatConditions(1).StopIfTrue = False

    End With

Open in new window

0
 
Patrick MatthewsCommented:
You might also consider this way, using R1C1 referencing instead of A1 referencing:

Sub Patrick()
    
    Dim rng As Range
    
    Set rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
    With rng
        .FormatConditions.Delete
        .FormatConditions.Add xlExpression, , "=WEEKDAY(RC,2)>5"
        With .FormatConditions(1)
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent6
                .TintAndShade = 0
            End With
            .StopIfTrue = True
        End With
    End With
    
End Sub

Open in new window



It takes a little getting used to for people accustomed to A1, but it handles the offsets in a much more intuitive fashion :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.