VBA Conditional Formatting Problem (simple?)

[randomwit]

Background: I am trying to use a macro to apply conditional formatting to each column in a selection (FormatArea).

Each column needs to have two conditional formats--one for if the numbers are greater than a cell and one for if they are less than another cell. These two cells are unique to each column (they are in rows 2 and 4 of each column).

I think my problem is in the Format1 property of the conditional formats. This code runs, but doesn't actually format the cells as expected.

HELP?

Sub AddConditionalFormats(FormatArea As Range)
    
    Dim col As Range
    Dim ucl As Range
    Dim lcl As Range
    
    FormatArea.FormatConditions.Delete
       
    For Each col In FormatArea.Columns
        
        Set ucl = Cells(4, col.Column)
        Set lcl = Cells(2, col.Column)
        
        col.Select
        
        'GREATER THAN UCL
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:=CStr(ucl.Address)
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        
        With Selection.FormatConditions(1).Font
            .Color = -16383844
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        
        'LESS THAN LCL
        'Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=$C$2"
        'Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        'With Selection.FormatConditions(1).Font
            '.Color = -16383844
            '.TintAndShade = 0
        'End With
        'Selection.FormatConditions(1).StopIfTrue = False
        
    Next col
              
End Sub

 

[randomwit]

Posting this gave me an idea that solved it!

Instead of ucl.Address i used ucl.Value....always the simple things

 

[randomwit]

On another note:

I realized that using .value will not link the formatting to the cell as I originally intended. The cells I want to link to are dynamic and the formatting should adjust accordingly...

Anyone know the answer to this one?