Conditional Formatting with VBA

[llane23]

Not working. I am obviously doing something wrong. I just want the G cell to highlight if it's the same as what is in the I cell. And then to go back to no highlight once the conditions are met.

Sub ConditionalFormatFirstNames()
Dim lr As Long
lr = ActiveSheet.Cells(Rows.count, 1).End(xlUp).Row
Dim LastNames As Range
Set LastNames = Range("I:I")
With ActiveSheet.Range("G2:G" & lr)
.FormatConditions.Delete
.FormatConditions.Add xlExpression, Formula1:="=$G2 = ""LastNames.Value"""
.FormatConditions(1).Interior.Color = RGB(156, 0, 6)
End With
End Sub

 

[skillilea]

Try this

Sub ConditionalFormatFirstNames()
    Dim lr As Long
    lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row
    Dim LastNames As Range
    Set LastNames = Range("I:I")
    
    With ActiveSheet.Range("G2:G" & lr)
        .FormatConditions.Delete
        .FormatConditions.Add xlExpression, Formula1:="=$G2=$I2"
        .FormatConditions(1).Interior.Color = RGB(156, 0, 6)
    End With
End Sub

 

[llane23]

Thank you for your help! This is highlighting G1, but I need it to highlight the actual cells that match. Am I kind of on the right track here?

 

[skillilea]

This should work. Test for a match in G/I cols

Sub ConditionalFormatFirstNames()
    Dim lr As Long
    lr = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).row

    With ActiveSheet.Range("G2:G" & lr)
        .FormatConditions.Delete
        .FormatConditions.Add xlExpression, Formula1:="=$G2=$I2"
        .FormatConditions(1).Interior.Color = RGB(156, 0, 6)
    End With
End Sub