Apply Conditonal Formatting based on a relative cell address in VBA

[pizzaboy]

I'm trying to add a couple of FormatConditions to a range based on Formulas (xlExpression?).


The range spans a couple of table columns so I need to build the formula based on a relative cell address so the formatting is referencing the cell's own value.


I have this working fine via Excel's Conditional Formatting, but I'm struggling with the VBA code.


Building the range was fine:

                'get the range
                rangeAdd = Range(tb & "[" & currentDay.ColumnName(formattingItem) & "]")
                
                
                'remove existing formatting
                rangeAdd.FormatConditions.Delete
                
                
                'add to the new range
                If Not rangeFormatting Is Nothing Then
                    Set rangeFormatting = Union(rangeFormatting, rangeAdd)
                Else
                    Set rangeFormatting = rangeAdd
                End If

The conditional formatting in Excel, looks like this:

=(AQ6="TOP")

How do I convert my first table cell reference so it reads "AQ6", and how do I build the formatting condition in VBA?

    Dim condition_Top As FormatCondition
    
    With rng
        Set condition_Top = .FormatConditions.Add(xlExpression, "=" & sAddress & "=""TOP""")
    End With
End Sub


Public Sub SetFormattingCondition(condition As FormatCondition, cellColor)


    With condition
        .Interior.Color = cellColor
    End With


End Sub

Any help would be greatly appreciated.


Thanks.

 

[pizzaboy]

Got everything working!

Public Sub SetFormattingCondition(rng As Range, expression As String, cellColor)
    Dim condition As FormatCondition
    
    With rng
        Set condition = .FormatConditions.Add(xlExpression, , "=" & expression)
    End With


    With condition
        .StopIfTrue = True
        .Interior.Color = cellColor
    End With
    
End Sub