1

I'm trying 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.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
M.W.
  • 19
  • 4
  • 4
    Best to avoid using `Selection`. Try just `.FormatConditions.Count`, to refer to the `With ActiveSheet.Range("E:E,C:C,A:A")`. – BigBen Dec 06 '19 at 13:13
  • 1
    See [this question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). You want to avoid the other uses of `Selection` too. – BigBen Dec 06 '19 at 13:14
  • ``` .FormatConditions.Count ``` Like this? – M.W. Dec 06 '19 at 13:16
  • No - just get rid of the `Selection` in those lines: `.FormatConditions(.FormatConditions.Count).SetFirstPriority` – BigBen Dec 06 '19 at 13:17

0 Answers0