Delete selected conditional formatting

[jfjoyner3]

(I'm new, please let me know if I violate the community rules.) This follows up on threads from 2019 (January 10) and 2005 (august 12). I used this command -- =CELL("protect",INDIRECT(ADDRESS(ROW(),COLUMN())))=(#REF!) -- in conditional formatting to make numbers red and this command -- CELL VALUE - "REPORT" -- to put a dark background in cells where I manually entered the text, "REPORT." This got out of hand and now I have 100,000 conditional formats spread among 150 tabs in a 25 mb workbook many of which now show #REF because I subsequently moved columns and row. I want to delete these two (but not other) conditional formats. I no longer need the red font for negative numbers or the dark background for "REPORT". As I read these and other posts, it seems like a VBA function can do it nicely, but I know enough about Excel to be dangerous and nothing at all about VBA. If someone his inclined to help it would be very much appreciated. [Thanks, great site!]

 

[Jimmypop]

Hi there...

Can you not delete directly from Conditional Formatting Rules Manager?

 

[jfjoyner3]

Sure, I tried that, it's more like this image, which is a little daunting.

 

[Jimmypop]

Is there any other Conditional Formatting that needs to stay on the sheet?

UPDATE: I saw now you want to keep others... just give us a moment...

 

[Alex Blakenburg]

Try this on a copy of your spreadsheet.
You may need to tweak the rules that I have used based on your post #1.

Sub DeleteConditionalFormatting()

    Dim sht As Worksheet
    Dim condRule As Variant
    Dim s_condFormula As String
    Dim i As Long
    Dim arrForm As Variant
    Dim strFormula As String
    
    For Each sht In ThisWorkbook.Worksheets
        If sht.Cells.FormatConditions.Count > 0 Then
        
            'Loop through all the Conditionals formattings
            For i = 1 To sht.Cells.FormatConditions.Count
                
                Set condRule = sht.Cells.FormatConditions.Item(i)
                s_condFormula = condRule.Formula1
                
                strFormula = "=CELL(""protect"",INDIRECT(ADDRESS(ROW(),COLUMN())))=(#REF!)"
                If s_condFormula = strFormula Then condRule.Delete
                
                strFormula = "=" & """REPORT"""
                If InStr(1, s_condFormula, strFormula, vbTextCompare) Then condRule.Delete

            Next i
        End If
    Next sht

End Sub

 

[jfjoyner3]

Wow, thanks, will try it and let you know. Thank you.

 

[jfjoyner3]

Wow, thanks, will try it and let you know. Thank you.

I get the error message Run-time error 9 Subscript out of range on this line
Set condRule = sht.Cells.FormatConditions.Item(i)

I wish I know how to fix this, but I don't.

 

[Alex Blakenburg]

Can you change the “For i = “ line to this.

For i = sht.Cells.FormatConditions.Count to 1 Step - 1