Use VBA to delete conditional formatting from a protected sheet

[Jaye Cavallo]

I am attempting to use VBA to delete conditional formatting on a protected worksheet. When I run the simple macro below, I receive a 400 error. However, if I unprotect the worksheet, remove the lines of code for unprotect/protect, the macro works without error and removes all the conditional formatting for the worksheet. Any suggestions are appreciated.

Sub RemoveConditionalFormatting()

Dim WS As Worksheet
Set WS = ActiveSheet

ActiveSheet.Unprotect Password:="hadley4000", UserInterfaceOnly:=True

WS.Range("A2:Z3569").Cells.FormatConditions.Delete

ActiveSheet.Protect Password:="hadley4000", UserInterfaceOnly:=True

End Sub

 

[Joe4]

Welcome to the Board!

Error 400 could indicate that there are issues with the Excel file (could be corrupted or infected) itself or your Excel installation.

Have a look at these two articles here:

VBA Error 400: What Are the Causes and How to Fix It? - VBA and VB.Net Tutorials, Education and Programming Services

Contents Introduction It is often said that debugging is part of programming, and through error messages, the programmer can easily identify where there are problems in his/her code. But as you can see from the above screenshot the VBA Error 400 is one of those

software-solutions-online.com

VBA Error 400 (How to Fix it)

In this tutorial, you will learn why VBA Error 400 occurs and how to deal with it while write a VBA code.

excelchamps.com

 

[Fluff]

The unprotect line should be

ActiveSheet.Unprotect Password:="hadley4000"

 

[Jaye Cavallo]

Thanks for the replay regarding error 400. I am going through the process now. Will let you know if I can resolve.

 

[Jaye Cavallo]

Thank you for the suggestion to correct my code, Fluff, for protecting/unprotecting a worksheet.

 

[Fluff]

Glad we could help & thanks for the feedback.