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.

Loop Through All the Controls of a VBA UserForm

There are various controls we can use inside a vba userform. When we design user interface, we use relevant controls according to the requirements of the user. We don’t need all the controls for all the applications we create. We put the controls according to how the user want to input, process and output data. Also there are some special controls like “Image” and “Scroll Bar”. This userform contains some widely used controls we use in our applications.

I haven’t put the controls in a meaningful order. Because I only created this to explain you how to access the each of these controls. Otherwise these controls should properly labeled and placed in a meaningful way. Also I didn’t use the conventional naming method to name the controls. These controls have their default names.

In some advance applications, we need to loop through all of these controls and take the actions according to the conditions. So in this post I will show you how to loop through all the controls of a userform. First, let’s try to loop through all the controls and print the name of each of the controls in immediate window.

Private Sub CommandButton1_Click() Dim c As Control For Each c In Me.Controls      Debug.Print c.Name Next End Sub

I have written the code in CommandButton1. So if we click the button we will get following result.

Sometimes we need to detect particular control types of a userform. So if we need to do that first we need to find a way to detect type of each of the controls. We can do it like this. This code will output control name with it’s type.

Private Sub CommandButton1_Click() Dim c As Control For Each c In Me.Controls      Debug.Print "Name - " & c.Name & " Type - " & TypeName(c) Next End Sub

You will get following result when you click the button.

Then if you modify this code little bit, you can read the values of only particular controls. Following example will print the values of textboxes.

Private Sub CommandButton1_Click() Dim c As Control For Each c In Me.Controls      If StrComp(TypeName(c), "Textbox", vbTextCompare) = 0 Then          Debug.Print c.Value      End If Next End Sub

First I entered some text before click the button.

When you click on the CommandButton1 you will get this result.