Deleting Multiple Conditional Formatting

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
3,212
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Are you looking to delete ALL Conditional Formatting rules on your sheet?
If so, why not just use:
VBA Code:
    Cells.FormatConditions.Delete
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0
Solution

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top