How to remove all conditional formatting from a workbook

Conditional formatting is a very powerful function. However, removing them can be a bit time consuming.

Fortunately, “Clear Rules from Selected Cells” and “Clear Rules from Entire Sheet” allows us to clear conditional formatting in bulk.

However, they don’t come in handy when we have hundreds of worksheets. Pressing “Clear Rules from Entire Sheet” for hundred times can be a real hassle.

Especially when your workbook is becoming slow to open, it can be really frustrating.

How to remove all conditional formatting from all worksheets?

This can be achieved by using VBA code. The VBA code cycles through each worksheet and removes all instances of conditional formatting found.

In this article, I will show you how to delete conditional formatting from a workbook with VBA code.

Sample

The following workbook contains 3 worksheets. Each worksheet has its own conditional formatting rule.

What we have to do

To clear conditional formatting from all worksheets.

VBA code

Read How to Insert & Run VBA code in Excel – VBA101 if you forget how to insert VBA code in Excel.

Application.ScreenUpdating = False and Application.ScreenUpdating = True is a great pair of code which helps speed up the execution of code.

You can remove this two lines of code and still get the result.

Sub removeallcond()
   'turn off normal action of showing changes and thus make the execution of code faster
    Application.ScreenUpdating = False
   'loop through sheets in the current workbook
        For Each sSheet In ThisWorkbook.Sheets
             'remove the conditional formatting from each cell
             sSheet.Cells.FormatConditions.Delete
        Next
   'turn on normal action of showing changes
    Application.ScreenUpdating = True
End Sub

Hungry for more useful Excel tips like this? Subscribe to our newsletter to make sure you won’t miss out on any of our posts and get exclusive Excel tips!