Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Remove Conditional Formatting From an Excel Sheet (VBA)

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.

Contact Form

Name

Email *

Message *

Popular Posts