Delete all Conditional Formatting Q

[Seanie]

What code could I use to delete all Conditional Formatting in the
active workbook?

Thanks

 

[Ron Rosenfeld]

What code could I use to delete all Conditional Formatting in the
active workbook?

Thanks

What version of Excel?

In Excel 2007, this code should work. I'm not sure about earlier
codes. I set a worksheet name because, for testing, it happens to be
a sheet that has a number of conditional format cells, but you
probably want to change that to activesheet or a specific sheet(s)
depending on your specific requirements:

================================
Option Explicit
Sub DeleteConditionalFormats()
Dim ws As Worksheet
Dim r As Range, c As Range
Set ws = Worksheets("Solver")
On Error GoTo NoCellsFound

ws.Cells.SpecialCells(xlCellTypeAllFormatConditions).FormatConditions.Delete
Exit Sub
NoCellsFound: MsgBox ("No Cells Found")
End Sub
=================================

 

[Seanie]

Thanks Ron, 2007 is the version

How can I tweak to delete on all sheets in the active workbook?

 

[Gord Dibben]

Iterate through the sheets.

Option Explicit
Sub DeleteConditionalFormats()
Dim ws As Worksheet
Dim r As Range, c As Range
On Error GoTo NoCellsFound
For Each ws In ActiveWorkbook.Worksheets
ws.Cells.SpecialCells(xlCellTypeAllFormatConditions).FormatConditions.Delete
Next ws
Exit Sub
NoCellsFound: MsgBox ("No Cells Found")
End Sub

Note: if any sheet has no CF you will get the error.


Gord Dibben MS Excel MVP

 

[Ron Rosenfeld]

Thanks Ron, 2007 is the version

How can I tweak to delete on all sheets in the active workbook?

You iterate through all the sheets, and rework the error handler to
keep track of sheets with no CF's.

You could also display more information if you want:

===================================
Option Explicit
Sub DeleteConditionalFormats()
Dim ws As Worksheet
Dim r As Range, c As Range
Dim sNoCF() As String
Dim i As Long, s As String
ReDim sNoCF(0)
For Each ws In ActiveWorkbook.Worksheets
On Error GoTo NoCellsFound
ws.Cells.SpecialCells(xlCellTypeAllFormatConditions).FormatConditions.Delete
Next ws

s = Join(sNoCF, vbLf)
MsgBox ("The following sheets did not have any cells with CF:" & s)

Exit Sub
NoCellsFound:
On Error GoTo 0
ReDim Preserve sNoCF(UBound(sNoCF) + 1)
sNoCF(UBound(sNoCF)) = ws.Name
Resume Next
End Sub
==================================

 

[Seanie]

What am I doing wrong on below which debugs with message "no cells
found", I tried to tweak to delete all CF in all sheets, without any
message box display


Sub DeleteConditionalFormats()
Dim ws As Worksheet
Dim r As Range, c As Range
On Error GoTo 0
For Each ws In ActiveWorkbook.Worksheets
ws.Cells.SpecialCells(xlCellTypeAllFormatConditions).FormatConditions.Delete
Next ws
Exit Sub
End Sub

 

[Ron Rosenfeld]

What am I doing wrong on below which debugs with message "no cells
found", I tried to tweak to delete all CF in all sheets, without any
message box display

You have no code to handle the error that will occur when there are no
conditionally formatted cells on a worksheet.

Sub DeleteConditionalFormats()
Dim ws As Worksheet
Dim r As Range, c As Range
On Error GoTo 0
For Each ws In ActiveWorkbook.Worksheets
ws.Cells.SpecialCells(xlCellTypeAllFormatConditions).FormatConditions.Delete
Next ws
Exit Sub
End Sub

Perhaps:

=======================
Option Explicit
Sub DeleteConditionalFormats()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
ws.Cells.SpecialCells(xlCellTypeAllFormatConditions).FormatConditions.Delete
Next ws
End Sub
==========================

 

[Seanie]

Thanks Ron, I thought "On Error GoTo 0" handled that

One further twist, delete all CF except in Sheets1; Sheet2; Sheet3?

 

[Ron Rosenfeld]

Thanks Ron, I thought "On Error GoTo 0" handled that

You need to read VBA HELP for the On Error statement
There is an error in my code in that it does not reset the error
function. It should read:

=================
Option Explicit
Sub DeleteConditionalFormats()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
ws.Cells.SpecialCells(xlCellTypeAllFormatConditions).FormatConditions.Delete
On Error GoTo 0
Next ws
End Sub
===================

One further twist, delete all CF except in Sheets1; Sheet2; Sheet3?

Just test to see which worksheet you are cycling on; then skip the
loop if it is a sheet you're not interested in.

===========================================
Option Explicit
Sub DeleteConditionalFormats()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Sheet1" And _
ws.Name <> "Sheet2" And _
ws.Name <> "Sheet3" Then
On Error Resume Next
ws.Cells.SpecialCells(xlCellTypeAllFormatConditions).FormatConditions.Delete
On Error GoTo 0
End If
Next ws
End Sub
======================================