VBA Conditional Formatting Formula Simple Error

[randomwit]

I am having problems with my code snippet below. I am almost certain that the error lies in Formula1:=CStr(ucl.Address).

I can get the code to work by changing it to ucl.Value, but I need the formatting to be linked to the ucl CELL, not it's value, so that it will update when the value in ucl changes...

I feel like i've tried most of the things I can think of, but no luck.

How do I get this to work the way I described above?

Help, please

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
...
...

 

[AlphaFrog]

Try something like this...

With col
    'GREATER THAN UCL
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:=[COLOR="Red"]ucl
    .FormatConditions(.FormatConditions.Count).Interior.ColorIndex = 38[/COLOR]
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
End With

This sets the CF format to a red backgroud. No need for col.Select

 

[randomwit]

That helped clean up the code a bit, but it does the same thing as applying the condition to a hard value (or ucl.value). Since the cells ucl and lcl are subject to change values, i need the formatting to adjust.

For example, say one data point in column C is higher than UCL when code executes. The cell text should be red (this code will do that). However, if UCL changes, such that the red cell is no longer greater, it should not have the formatting anymore (this code cannot do that).

If i apply conditional formatting manually, it will do this, but I cannot emulate it using vba.

Sub AddConditionalFormats(FormatArea As Range)
    
    Dim col As Range
    Dim ucl As Range
    Dim lcl As Range
    
    Application.Volatile
    FormatArea.FormatConditions.Delete
       
    For Each col In FormatArea.Columns
        
        Set ucl = Cells(4, col.Column)
        Set lcl = Cells(2, col.Column)
        
        With col
            'GREATER THAN UCL = RED TEXT
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:=ucl
            .FormatConditions(.FormatConditions.Count).Font.Color = -16383844
            .FormatConditions(.FormatConditions.Count).Font.TintAndShade = 0
            .FormatConditions(.FormatConditions.Count).StopIfTrue = False
        End With
        
        With col
            'LESS THAN LCL = BLUE TEXT
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:=lcl
            .FormatConditions(.FormatConditions.Count).Font.Color = -4165632
            .FormatConditions(.FormatConditions.Count).Font.TintAndShade = 0
            .FormatConditions(.FormatConditions.Count).StopIfTrue = False
        End With
        
    Next col
              
End Sub

 

[AlphaFrog]

Try this...

...Formula1:="=" & ucl.Address

 

[randomwit]

For what it's worth, the answer is a little tricky. Finally got it, though.

Formula1:=("=" & ucl.Address)

will link the formatting to the cell, not the value.

 

[randomwit]

Oh, I see you're one step ahead of me Apha. Thanks for the help!