Quickly Clear Conditional Formatting

April 2, 2008 – 2:29 am by JP

Here is a simple sub that deletes conditional formatting from a selection. The code does some prelim checking to see that it is actually acting on a range, and it mimics Excel’s usual behavior (like a good VBA procedure should, if I may say so) by acting on the entire worksheet if you only select a single cell. However I recommend just selecting the range before you run the code.


Sub Clear_Cond_Formatting()
Dim rng As Excel.Range

If TypeName(Selection) <> "Range" Then Exit Sub

Select Case Selection.Cells.count
    Case 1
       Set rng = Cells
    Case Else
       Set rng = Selection
End Select

On Error Resume Next
rng.FormatConditions.Delete
On Error GoTo 0
End Sub

Enjoy,
JP

If you enjoyed this page, share it!

Print This Post |

Email This Post | rss

Subscribe to Posts Feed | rss

Subscribe to Comments

Filed Under: Excel, VBA
Tags: conditional formatting, Excel, VBA

3 Responses to “Quickly Clear Conditional Formatting”:
Aren’t the second and third “If Typename” lines redundant? If it’s not a range, then it’s already left the sub.

By Jon Peltier on Apr 3, 2008
Thanks Jon. That’s what I get for cutting and pasting without reading!

–JP

By JP on Apr 3, 2008
Here is the revised code…

Sub Clear_Cond_Formatting()
Dim rng As Excel.Range

If TypeName(Selection) <> “Range” Then Exit Sub

Select Case Selection.Cells.count
Case 1
Set rng = Cells
Case Else
Set rng = Selection
End Select

On Error Resume Next
rng.FormatConditions.Delete
On Error GoTo 0

End Sub

By JP on Apr 3, 2008