Remove all conditional formatting from a workbook

[davez]

Hi, hoping someone can help -

I have a large workbook which contains many varied worksheets, many of which have conditional formatting rules of one sort or another.

As the workbook is becoming increasingly slow to open/close/update I am looking to trim it down a bit & was hoping that someone might be able to provide code which would cycle through each worksheet & remove all instances of conditional formatting found.

I have had a good search here to try & find an answer with no luck, so many thanks in advance for any advice given.

 

[sulakvea]

try this:

Sub removecond()
Application.ScreenUpdating = False
For Each TmpSht In ThisWorkbook.Sheets
 TmpSht.Cells.FormatConditions.Delete
Next
Application.ScreenUpdating = True
End Sub

 

[Tonto54]

To remove the conditional formatting from a specific range of cells:

1. start by selecting the range.

2. Click the “Conditional Formatting” button.

3. Choose “Clear Rules…” and then “Clear Rules from Selected Cells”.


To remove all the conditional formatting from the entire worksheet:

1. Click the “Conditional Formatting” button.

2. Choose “Clear Rules…” and then “Clear Rules from Entire Sheet”.

 

[davez]

try this:

Sub removecond()
Application.ScreenUpdating = False
For Each TmpSht In ThisWorkbook.Sheets
 TmpSht.Cells.FormatConditions.Delete
Next
Application.ScreenUpdating = True
End Sub

thanks sulakvea, worked beautifully & my workbook is now much more user friendly

 

[Churchy LaFemme]

sulakvea, should I expect this to work in Excel 2007? I am not having the expected result. Running the macro results in no error messages and it seems bug free.

There's always the outside chance that the Excel setup on my work computer has something that conflicts with this module.

Any thoughts?

 

[davez]

Churchy, I am using Excel 2007 & have used the code many time now without any problems.

The only suggestion I can make is to check that the code is located in the workbook you want to remove the formatting from.

 

[Churchy LaFemme]

I'll try that - right now I keep my macros in a unique workbook. (Our frequent office-wide updates wipe out any changes one makes to MS templates!).

The others work as long as that workbook is also open, but it won't hurt to try putting the code in the workbooks that are full of conditional formatting that needs to go when DRAFT version are saved as FINAL.

Thanks!