Changing Conditional Formatting rules that were used with VBA from absolute to relative references

[Moonbeam111]

I've looked around the message board and I could not really find an answer but I hope I'm not overthinking this.

I have a vba code that looks for the value in H22 and checks to see which cell matches that value in ranges("D21:D25")and applies a background color of yellow on it.

For Each ThisCell1 In Range("H22")
For Each ThisCell3 In Range("D21:D25")
If ThisCell1.Value = ThisCell3.Value Then
ThisCell3.FormatConditions.Add(Type:=xlExpression, Formula1:=True).Interior.Color = vbYellow
End If
Exit For
Next ThisCell3
Next ThisCell1

It works fine by itself. But the ranges D21:D25 are part of a table. When I sort that table, the interior yellow color does not change with the original cell it matched. It just stays with the original cell.

So basically this:

Is what it looked like originally.

But here when i sorted Z-A. It did this:

The VBA applied color should still be on Blue but its actually stuck on Pink (Range"D21")

How do I go about fixing this?

 

[Alex Blakenburg]

That only has a chance of working if H22 is outside of the table range.

The below has 2 options, in the table option it would be preferable to use the actual table name rather than "1"

Sub ApplyConditionalFormatting()

    Dim rng As Range
    
    Set rng = ActiveSheet.Range("D21:D25")
    
    rng.FormatConditions.Add Type:=xlExpression, Formula1:="=$h$22=" & rng.Cells(1).Address(0, 0)
    rng.FormatConditions(1).Interior.Color = vbYellow

End Sub


Sub ApplyConditionalFormatting_Table()

    Dim rng As Range
    
    Set rng = ActiveSheet.ListObjects(1).ListColumns("Color").DataBodyRange
    
    rng.FormatConditions.Add Type:=xlExpression, Formula1:="=$h$22=" & rng.Cells(1).Address(0, 0)
    rng.FormatConditions(1).Interior.Color = vbYellow

End Sub