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.
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.
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!