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.
VBA:
-
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
Print This Post
|
Email This Post
|
Permalink
|
Subscribe now!
Filed Under: Excel, VBA
Tags: conditional formatting, Excel, VBA
This post has 134 views since April 2, 2008 – 2:29 am.
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