Convert conditional formatting into a Macro?

[donnabee]

Hi all.

Running latest version of Excel through Microsoft 365 using Windows 10 Pro.

I am running conditional formatting in my workbook with varying degrees of success. Sometimes it's great and has zero problems, then suddenly the conditions have changed themselves randomly and I don't know why - Google tells me this is a common problem? By changed I mean it has added more rules in the conditional formatting - rules I have not asked for not put in there myself - or added some more numbers and ranges onto my current conditions.

It's brilliant when it works, but when it changes I only get half the line coloured in or something else "breaks" in the conditions.

As you can see from the photo attached, the formula is relatively simple.
Color the line from A2 to AC20550 with either green, yellow or red - dependant on if number 1, 2 or 3 is typed into column AB
1 = green (hex #C6EFCE)
2 = yellow (hex #FFEB9C)
3 - red (hex #FFC7CE)
(This colouring also then has it's own macro assigned to perform another set of steps - but that is irrelevant to what I am trying to achieve here.)

I Googled if assigning a Macro was a possibility, but it appears it is, though I quickly became lost.
I edited an example Macro I found with a tutorial, and it just won't work:

Sub formatting()

'Defining the variables:
Dim rng As Range
Dim condition1 As FormatCondition, condition2 As FormatCondition, condition3 As FormatCondition

'Fixing/Setting the range on which conditional formatting is to be desired
Set rng = Range("A2", "AC20550")

'Defining and setting the criteria for each conditional format
Set condition1 = rng.FormatConditions.Add(xlCellValue, xlEqual, "=1")
Set condition2 = rng.FormatConditions.Add(xlCellValue, xlEqual, "=2")
Set condition3 = rng.FormatConditions.Add(xlCellValue, xlEqual, "=3")

'Defining and setting the format to be applied for each condition
With condition1
.Font.Color = #C6EFCE
End With

With condition2
.Font.Color = #FFEB9C
End With

With condition3
.Font.Color = #FFC7CE
End With

End Sub

Any assistance is gratefully accepted, thank you

 

[rlv01]

Try this:

Sub formatting()

'Defining the variables:
    Dim rng As Range
    Dim condition1 As FormatCondition, condition2 As FormatCondition, condition3 As FormatCondition

    'Fixing/Setting the range on which conditional formatting is to be desired
    Set rng = Range("A2", "AC20550")

    rng.FormatConditions.Delete                       ' clear existing format conditions

    'Defining and setting the criteria for each conditional format
    Set condition1 = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$AB2=1")
    Set condition2 = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$AB2=2")
    Set condition3 = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$AB2=3")

    'Defining and setting the format to be applied for each condition
    With condition1
        .Font.Color = RGB(86, 153, 97)
        .Interior.Color = RGB(196, 239, 207)
    End With

    With condition2
        .Font.Color = RGB(207, 163, 89)
        .Interior.Color = RGB(255, 234, 164)
    End With

    With condition3
        .Font.Color = RGB(170, 50, 77)
        .Interior.Color = RGB(255, 199, 206)
    End With
End Sub

(Tip: when posting code, please try to use 'code tags' to format the code as I have done above

How to Post Your VBA Code

as it makes the code easier to read.)

 

[donnabee]

Thank you so much for this, it works perfectly!!