VBA and condition formating

[mduntley]

Tried to do a google search, but unable to find it. I need to get a way to get the current cell is active to apply a condition formating. It change all of the time so it is not a Set location.

My condition formatting

If current cell has the word NOT in it to change the cell into the red.

 

[JLGWhiz]

If you do not have any other Worksheet_Change or Worksheet-SelectionChangePrivate event code in the sheet code module then you could use the code below to do what you want. It is unclear whether you want it to apply as changes are made or if you want it to apply for existing data in a cell, so the two even codes below will cover either instance.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    If InStr(UCase(Target.Value), "NOT") > 0 Then
        Target.Interior.Color = vbRed
    End If
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
    If InStr(UCase(Target.Value), "NOT") > 0 Then
        Target.Interior.Color = vbRed
    End If
Application.EnableEvents = True
End Sub