vba - Create a conditional formating macro for text value on Excel

Very often I need to create conditional formating rules on my excel worksheets, not always on the same range, to format the text color depending on what's written.

The most common situation is turning all the cells in the range that have the text "Effective" green and bold, and "Not effective" red and bold.

I tried to create this macro using the Record Macro function on the Developer tab, but it didn't work, the code was blank.

As I have zero knowledge on VBA, I was wondering if somebody could give me a help creating this macro.

Definitions:

• There's no fixed range, it needs to capture the selected range;


• Format based on text, if "Effective" green and bold, if "Not effective" red and bold.


• Only for one sheet.

[Solved]

Sub EffectiveNot()
'
' EffectiveNot Macro
'
    Dim rStart As Range
    Set rStart = Selection
    Selection.FormatConditions.Add Type:=xlTextString, String:="Effective", _

        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .Color = -11489280
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlTextString, String:="Not effective", _

        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub