Conditional Formatting for Whole Sheet

Question

I'm trying to turn any cell that matches a preset value to a color.

If cells values are:

• S-DAYS, C-DAYS, DAYS it will turn the cell BLUE with black text
• E SWING, S-E SWING, C-E SWING it will turn Green with black text
• L SWING, S-L SWING, C-L SWING it will turn Light Purple with black text
• LATES, S-LATES, C-LATES it will turn Gray with black text
• AOT will turn Yellow with black text
• VAC, OUT, MIL, TRAIN it will turn the cell BLACK with White Text

I recorded the following. How do I to make it apply automatically to the sheet without needing to be prompted?

Sub DAConditionalFormating()
'
' DAConditionalFormating Macro
'

'
    Range("D14:XFD999").Select

    Selection.FormatConditions.Add Type:=xlTextString, String:="DAYS", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlTextString, String:="E SWING", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlTextString, String:="LATES", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249946592608417
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlTextString, String:="VAC", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

Answer 1

Applying conditional formatting to a whole sheet is a really, really bad idea. The calculation will kick in every time you edit ANY cell in sheet.

Instead of conditional formatting the whole sheet, use code to format only the cell that was just changed. Format the cell fill and the font.

Run this code in a Worksheet_Change event in the Sheet module and let it work on the target cell. Then it will run fast and change only the cell that was just changed.

Something like this. Add more ElseIF as required. The code goes into the Sheet module of the sheet.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Value = "foo" Then
    With Target.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
    End With
ElseIf Target.Value = "bar" Then
    With Target.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
    End With
End If

End Sub