(Solved) – EXCEL conditional formatting Run-time error ‘9’ subscript out of range

(solved)-–-excel-conditional-formatting-run-time-error-‘9’-subscript-out-of-range

Hi I try to make a macro in VBA excel to apply a few conditional formats.
See below

Private Sub CommandButton1_Click()

'Delete conditional formats
    Sheets("Results").Cells.FormatConditions.Delete
'Red formats
    Sheets("Results").Select
    With ActiveSheet.Range("C:C,A:A")
    .FormatConditions.AddUniqueValues
    .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    .FormatConditions(1).DupeUnique = xlDuplicate
    End With
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

'Blue formats
    With ActiveSheet.Range("E:E,C:C,A:A")
    .FormatConditions.AddUniqueValues
    .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    .FormatConditions(1).DupeUnique = xlUnique
    End With
    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 15773696
    .TintAndShade = 0
    End With

    Selection.FormatConditions(1).StopIfTrue = False
End Sub

The error pops up in these lines of code:

.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

Sometimes it works sometimes it doesn’t.
I recorded this macro and was working fine few days ago.

Leave a Reply

Your email address will not be published. Required fields are marked *