Macro to apply formula based condition cell formatting

Dear all,

 

I have been trying to record a macro to apply formula based conditional formatting to a table in a worksheet.

 

Recording the macro with the "use relative references" option selected doesn't seem to work at all.

 

Recording the macro with the "use relative references" option off and editing it a bit I get the following code:

 

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A2:G1048576").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$F2<TODAY()"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("A2:G1048576").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$F2<(TODAY()+30)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("A2:G1048576").Select
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$F2<(TODAY()+60)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("A2:G1048576").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=ISBLANK($F2)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

This doesn't quite work as I want it to:

The resulting conditional formatting from the macro is attached as is the desired result.

I suspect I am doing something wrong with the mixed references but haven't manged to find the solution by searching.

Many thanks

 

James