VBA Conditional Formatting with xlExpression

[Josh_197]

Hi -

I have written the following code to try and create a toggle each time the code is executed for conditional formatting. I am running into an error and have tried a few different variations of this code. Any advice is greatly appreciated.


Sub change_alternateshading()

Select Case Selection.FormatConditions.Add Type:=xlExpression

Case "=MOD(ROW(),2)"
Selection.FormatConditions.Delete

Case Else
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -4.99893185216834E-02
End With
Selection.FormatConditions(1).StopIfTrue = False

End Select
End Sub


Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
Will you only ever have one conditional format rule, on the cells you select?

 

[Josh_197]

Hi & welcome to MrExcel.
Will you only ever have one conditional format rule, on the cells you select?

No, there could be more than one conditional format rule

 

[Fluff]

You could try this on a copy of your workbook

Sub Josh()
   Dim i As Long
   Dim Flg As Boolean
   
   With Selection
      For i = 1 To .FormatConditions.Count
         If .FormatConditions(i).Formula1 = "=MOD(ROW(),2)" Then
            .FormatConditions(i).Delete
            Flg = True
         End If
      Next i
      If Not Flg Then
         .FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)"
         .FormatConditions(.FormatConditions.Count).SetFirstPriority
         With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -4.99893185216834E-02
         End With
      End If
   End With
End Sub

Although you could easily get some very odd results, so I would not really recommend it.