Deleting Multiple Conditional Formatting

[Jeffrey Mahoney]

I have code that removes previous Conditional Formatting (CF) and then creates new ones. It works to remove one CF, but errors out after that. The problem with the code below is that for each CF I delete there is one less CF to evaluate. In other words, if I have three CFs and delete the first one, #2 shifts into #1 position and #3 shifts into #2 position. This prevents the code from evaluating all the CFs and errors out at the end because there are no longer that many CFs to evaluate.

How can I evaluate all the CFs and delete them all at once?

On Error Resume Next
  For Each CF In Sel.FormatConditions
    If CF.Type = xlExpression And InStr(CF.Formula1, "=AND(") > 0 And InStr(CF.Formula1, "<1000") > 0 Then
      CF.Delete
    End If
  Next CF
  On Error GoTo 0

 

[Joe4]

Are you looking to delete ALL Conditional Formatting rules on your sheet?
If so, why not just use:

    Cells.FormatConditions.Delete

 

[Jeffrey Mahoney]

Sorry, I thought about for a little bit and realized I need to step from the last to the first. I only want to remove some of the CFs for the range.

CFCnt = Sel.FormatConditions.Count
  On Error Resume Next
  For X = CFCnt To 1 Step -1    ' Sel.FormatConditions
    Set CF = Sel.FormatConditions(X)
    If CF.Type = xlExpression And InStr(CF.Formula1, "=AND(") > 0 And InStr(CF.Formula1, "<1000") > 0 Then
      CF.Delete
    End If
  Next X
  On Error GoTo 0