Conditional Formatting Stripper: Clear Years of Accumulated Rules in One Click

TL;DR: This macro scans every sheet and reports all conditional formatting rules — color scales, icon sets, highlight rules — with a per-sheet breakdown. On confirm, it deletes them all in one pass. Clean slate, zero leftover formatting ghosts.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with conditional formatting rules you want to remove

Limitations:

• Does not remove formatting applied via cell styles, table styles, or direct formatting — only conditional formatting rules
• Protected sheets are skipped (the macro reports them separately)
• The delete is permanent — if you want to preserve some rules, review the per-sheet breakdown in the confirmation message before clicking Yes

#The Macro

Option Explicit

Sub StripConditionalFormatting()
    ' ── Conditional Formatting Stripper ────────────────
    ' Scans every sheet for conditional formatting rules.
    ' Reports a per-sheet breakdown, then deletes all
    ' rules on confirmation. Protected sheets are
    ' skipped and reported.
    '
    ' Run this before applying your own formatting to
    ' inherited workbooks with years of accumulated rules.
    ' ────────────────────────────────────────────────────

    Dim ws As Worksheet
    Dim totalSheets As Long
    Dim totalRules As Long
    Dim msg As String
    Dim skipped As String
    Dim protCount As Long

    totalSheets = 0
    totalRules = 0
    msg = ""
    skipped = ""
    protCount = 0

    ' ── Step 1: Count rules on each sheet ──────────────
    For Each ws In ThisWorkbook.Worksheets
        On Error Resume Next
        Dim ruleCount As Long
        ruleCount = ws.Cells.FormatConditions.Count
        Dim isProtected As Boolean
        isProtected = ws.ProtectContents
        On Error GoTo 0

        If isProtected Then
            protCount = protCount + 1
            skipped = skipped & "'" & ws.Name & "' (protected), "
        ElseIf ruleCount > 0 Then
            msg = msg & "'" & ws.Name & "' (" & ruleCount & _
                  " rule" & IIf(ruleCount = 1, "", "s") & "), "
            totalSheets = totalSheets + 1
            totalRules = totalRules + ruleCount
        End If
    Next ws

    ' ── Step 2: Report and confirm ─────────────────────
    If totalSheets = 0 And protCount = 0 Then
        MsgBox "No conditional formatting found on any sheet.", _
               vbInformation, "Nothing to Strip"
        Exit Sub
    End If

    If totalSheets = 0 And protCount > 0 Then
        MsgBox "No conditional formatting found on unprotected sheets." & _
               vbCrLf & vbCrLf & protCount & " protected sheet(s) skipped: " & _
               vbCrLf & Left(skipped, Len(skipped) - 2), _
               vbInformation, "Nothing to Strip"
        Exit Sub
    End If

    msg = Left(msg, Len(msg) - 2)

    ' Build the confirmation string
    Dim confirmMsg As String
    confirmMsg = "Found " & totalRules & " conditional formatting rule(s) " & _
                 "across " & totalSheets & " sheet(s):" & vbCrLf & vbCrLf & _
                 msg

    If protCount > 0 Then
        confirmMsg = confirmMsg & vbCrLf & vbCrLf & protCount & _
                     " protected sheet(s) will be skipped: " & vbCrLf & _
                     Left(skipped, Len(skipped) - 2)
    End If

    confirmMsg = confirmMsg & vbCrLf & vbCrLf & _
                 "Remove ALL conditional formatting from these sheets?"

    If MsgBox(confirmMsg, vbYesNo + vbQuestion, _
              "Confirm Remove") = vbNo Then
        MsgBox "No changes made.", vbInformation, "Cancelled"
        Exit Sub
    End If

    ' ── Step 3: Strip all conditional formatting ───────
    Application.ScreenUpdating = False

    Dim cleanCount As Long
    Dim failCount As Long
    cleanCount = 0
    failCount = 0

    On Error GoTo CleanUp

    For Each ws In ThisWorkbook.Worksheets
        If Not ws.ProtectContents Then
            On Error Resume Next
            ws.Cells.FormatConditions.Delete
            If Err.Number = 0 Then
                cleanCount = cleanCount + 1
            Else
                failCount = failCount + 1
            End If
            On Error GoTo CleanUp
        End If
    Next ws

    Application.ScreenUpdating = True

    ' ── Step 4: Final report ───────────────────────────
    Dim resultMsg As String
    resultMsg = "Removed conditional formatting from " & _
                cleanCount & " sheet(s)."

    If protCount > 0 Then
        resultMsg = resultMsg & vbCrLf & protCount & _
                    " sheet(s) skipped (protected)."
    End If

    If failCount > 0 Then
        resultMsg = resultMsg & vbCrLf & failCount & _
                    " sheet(s) failed (check for grouped sheets)."
    End If

    MsgBox resultMsg, vbInformation, "Done"
    Exit Sub

CleanUp:
    Application.ScreenUpdating = True

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#Count first, delete second

The macro runs two passes. The first pass counts FormatConditions on every sheet without touching anything. This lets you see exactly what will be deleted before any action is taken:

ruleCount = ws.Cells.FormatConditions.Count

The confirmation message shows a per-sheet breakdown: 'Sched-A' (3 rules), 'Fixed Assets' (8 rules), 'TB' (1 rule). If you see something unexpected — like a rule on the Summary sheet you want to keep — you can cancel with zero consequences.

#Protected sheets are handled gracefully

isProtected = ws.ProtectContents

Excel won’t let you delete conditional formatting from a protected sheet, and attempting it raises a runtime error. The macro detects protected sheets upfront, lists them in the confirmation message, and skips them during the delete pass. No runtime errors, no silent failures.

#One line does all the work

ws.Cells.FormatConditions.Delete

This single call removes every conditional formatting rule from every cell on a sheet — color scales, data bars, icon sets, cell highlight rules, top/bottom rules, and custom formula-based rules. It’s the VBA equivalent of selecting every cell and clicking “Clear Rules from Entire Sheet,” but applied to the entire workbook in one run.

#Why clean formatting matters before you apply your own

Conditional formatting rules stack. If a sheet has a red-yellow-green color scale from 2024 and you apply a new duplicate-highlight rule, the two interact unpredictably. The color scale might override your highlight. The order matters and is invisible. Starting from a clean slate ensures your formatting behaves exactly as you expect.

#Error handling for edge cases

On Error Resume Next
ws.Cells.FormatConditions.Delete
If Err.Number = 0 Then
    cleanCount = cleanCount + 1
Else
    failCount = failCount + 1
End If
On Error GoTo CleanUp

Some edge cases — grouped sheets, very hidden sheets, or sheets with corrupted conditional formatting — can cause the delete to fail. The macro uses On Error Resume Next for each individual delete and tracks failures separately, so one bad sheet doesn’t stop the rest of the workbook from being cleaned.

#Adapt It