Conditional formatting enables you to format cells according to certain criterias. This is very useful when analyzing and presenting the data. But when we develop some vba programs, sometimes we need to remove existing conditional formatting of whole sheet or specific ranges to avoid it interfere with result. This is needed if our program also format the cells according to some criterias. So in this post let’s learn how to remove conditional formatting programmatically. It is easier than you might think.
Let’s consider this sample data.
Two conditional formatting criterias are applied to this worksheet. First one is applied to range C4:C17 and second one is applied to range D4:D17. Cells are highlighted in yellow color in C4:C17 range if the cell value is greater than 40. And cells are highlighted in light red color in D4:D17 range if the cell values are less than 5000.
Now let’s learn how to remove conditional formatting of whole sheet. We can do it using following code.
|
Sub RemoveConditionalFormatting() Dim WS As Worksheet Set WS = ActiveSheet WS.Cells.FormatConditions.Delete End Sub |
This will remove conditional formatting from activesheet. So If we run this macro we will get follwing result.
Sometimes you may need to remove conditional formatting from specific area of your sheet. Not from whole sheet. So if we want to remove conditional formatting from certain range we can do it like this.
|
Sub RemoveConditionalFormattingFromRange() Dim WS As Worksheet Set WS = ActiveSheet WS.Range("D4:D17").Cells.FormatConditions.Delete End Sub |
This will remove conditional formatting from only D4:D17 range. You can replace “D4:D17” of above code with the range you want. You will get following result when you run above code.
Also if we need, we can remove conditional formatting from entire column or row as well. Following code will remove conditional formatting from column C.
|
Sub RemoveConditionalFormattingFromEntireColumn() Dim WS As Worksheet Set WS = ActiveSheet WS.Columns(3).Cells.FormatConditions.Delete End Sub |
Here is the result you will get if you run above code for sample data.
And this next examples shows how to remove conditional formatting from multiple rows.
|
Sub RemoveConditionalFormattingFromRows() Dim WS As Worksheet Set WS = ActiveSheet WS.Rows("1:100").Cells.FormatConditions.Delete End Sub |
This will remove conditional formatting from rows 1 to 100.
