VBA & Macro Problems

How to Use a Macro to Remove Conditional Formatting in Excel

Huma Ashraf Ch Sep 30, 2026 868 views

Question details

The user needs to remove excessive conditional formatting rules across an entire workbook using a VBA macro to resolve performance issues.

How to Use a Macro to Remove Conditional Formatting in Excel

Product: Excel 2021
Device & OS: not provided
Scenario: An Excel workbook contains too many conditional formatting rules, causing the file to become unstable, lag, or crash during use.
Observed behavior: The application experiences instability or crashes due to the heavy processing required by excessive conditional formatting rules.

Before you start

Before running any VBA macro that deletes data or formatting, ensure you save a backup copy of your workbook, as macro actions cannot be undone using the standard Undo feature.

Solution 1Recommended

Remove Conditional Formatting from All Worksheets using VBA

Use a VBA loop to iterate through every worksheet in your workbook and instantly delete all conditional formatting rules, saving you from manually clearing them sheet by sheet.

This macro will process the entire active workbook. It iterates through each worksheet and deletes all FormatConditions associated with the cells. This is highly effective for cleaning up old or excessive rules that are bogging down your file's performance.

Open the VBA Editor

Press the ALT + F11 keys on your keyboard to open the Microsoft Visual Basic for Applications window.

Insert a New Module

Click on 'Insert' in the top menu bar, and then select 'Module' to create a blank workspace for your code.

Enter the Macro Code

Copy and paste the following script into the module window: Sub RemoveConditionalFormatting() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Cells.FormatConditions.Delete Next ws End Sub

Run the Macro

Press F5 or click the green 'Run' triangle button in the top toolbar to execute the macro. All conditional formatting across the workbook will be removed instantly.

Remove Conditional Formatting from All Worksheets using VBA

Process Complete: Once the macro finishes running, you can close the VBA editor and return to your workbook. You should immediately notice improved file performance.

Solution 2

Clear Rules Manually Using the Ribbon (Alternative)

If you only need to clear formatting from a specific sheet and prefer not to use VBA, you can easily remove the rules using Excel's built-in Conditional Formatting menu.

Free Microsoft Office alternative

Manage Complex Workbooks Smoothly with WPS Office

If excessive conditional formatting is causing your current spreadsheet software to crash or slow down, consider switching to WPS Office. It is a highly compatible, lightweight, and completely free alternative that handles complex data gracefully.

1. Download and Install: Visit the official WPS website to download and install WPS Office Free on your computer.
2. Open Your Workbook: Launch WPS Spreadsheet and easily open your existing Excel workbooks without losing any standard formatting.
3. Manage Your Data: Enjoy a fast, crash-free experience while managing heavy conditional formatting and data rules.

Fully compatible with Microsoft Excel formats, including .xlsx, .xls, and macro-enabled .xlsm files.Lightweight architecture ensures faster loading times and seamless editing for large datasets.Familiar ribbon user interface allows you to transition without a steep learning curve.Built-in conditional formatting management tools that are easy to use and highly responsive.

Frequently Asked Questions

Why isn't the macro recorder capturing my conditional formatting deletion?

The built-in macro recorder sometimes fails to capture specific formatting actions, such as clicking 'Clear Rules' from the ribbon interface. Writing a direct VBA script using the Cells.FormatConditions.Delete method is the most reliable way to automate this process.

Can I remove conditional formatting from a specific range using VBA instead of the whole sheet?

Yes. To clear rules from a specific range, simply modify your VBA code to target those specific cells. For example, use Range("A1:D10").FormatConditions.Delete instead of Cells.FormatConditions.Delete inside your macro.

Will deleting conditional formatting remove my standard cell colors and borders?

No, standard cell formatting such as manual background colors, borders, and font styles will remain unaffected. Only the dynamic styles applied by the conditional formatting rules will be permanently removed.

How can I view all active conditional formatting rules in my workbook before deleting them?

To review your rules, go to the Home tab, click on Conditional Formatting, and select 'Manage Rules'. In the Rules Manager dialog box, change the 'Show formatting rules for' dropdown to 'This Worksheet' to see everything currently applied.

Excel & Data Problems