Conditional Formatting Clear From Entire WorkBOOK

[tjscott0310]

We run a real estate business and each transaction is a tab in a workbook. There are a few hundred built up over the years and the file is getting too large.

We would like to move older ones to an Archive Workbook for that year. It's painfully slow. I am pretty sure the conditional formatting is the culprit.

How can I clear the conditional formatting from the entire workbook in one fell swoop? Not just one sheet at a time?

I am using Excel 2019 as part of Office 365 Small Business

Thanks,

 

[mabbutt]

Have a look here

 

[yky]

Always test the code on a copy of your data file, not the data file itself.

Open the workbook, add the following module (Developer/Insert/Module), run it, save the file without the VBA script, i.e. save it as .xlsx file (I assume that the original file is in .xlsx format.)

If Developer is not on your menu bar, search the net to see how to add it to the menu bar.

Sub delete_all_conditional_formatting()

'this script will delete all conditional formattings
'in the entire workbook

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets

ws.Cells.FormatConditions.Delete

Next ws

End Sub

 

[tjscott0310]

Have a look here

I did find that and couldn't get it to work. Could it be that it's from 2010 and likely for Excel 2007? Thank you, though, for trying to help.

 

[tjscott0310]

Always test the code on a copy of your data file, not the data file itself.

Open the workbook, add the following module (Developer/Insert/Module), run it, save the file without the VBA script, i.e. save it as .xlsx file (I assume that the original file is in .xlsx format.)

If Developer is not on your menu bar, search the net to see how to add it to the menu bar.

Sub delete_all_conditional_formatting()

'this script will delete all conditional formattings
'in the entire workbook

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets

ws.Cells.FormatConditions.Delete

Next ws

End Sub

Thanks! I will try this in the next day or two and report back.

 

[tjscott0310]

Always test the code on a copy of your data file, not the data file itself.

Open the workbook, add the following module (Developer/Insert/Module), run it, save the file without the VBA script, i.e. save it as .xlsx file (I assume that the original file is in .xlsx format.)

If Developer is not on your menu bar, search the net to see how to add it to the menu bar.

Sub delete_all_conditional_formatting()

'this script will delete all conditional formattings
'in the entire workbook

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets

ws.Cells.FormatConditions.Delete

Next ws

End Sub

I tried this one one file, "Transaction Management (1).xlsx". Worked beautifully! Thank you.

When I went to the other file, I am getting a debug error. "Run-time error'13': Type mismatch. When I select "Debug", I get the screen shot below.

 

[yky]

Interesting! What is ws at that moment as seen in the watch or immediate window?

 

[Fluff]

Try

For Each ws In ThisWorkbook.WorkSheets

You may have sheets that are not worksheets.

 

[tjscott0310]

Interesting! What is ws at that moment as seen in the watch or immediate window?

I'm sorry, I don't know what the watch or immediate window are. I never have used or learned about this part in Excel.

In the VBA Project window, if I scroll up, I see two charts. I have no idea where they are or why.

The solution below, "For Each ws In ThisWorkbook.Worksheets" work perfectly.

Thanks so much for you help! Saved me a few hours of work.

Merry Christmas!!

 

[Fluff]

Glad we could help & thanks for the feedback