EDUCBA

Home VBA VBA Resources VBA Tips VBA Conditional Formatting
 

VBA Conditional Formatting

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

Excel VBA Formatting

Conditional Formatting in Excel VBA

In excel, we all have used Conditional Formatting for highlighting duplicate values. Majorly conditional formatting is used to get duplicate values. We can highlight duplicate values in many ways. We can highlight the duplicate values, range specific values and also can define the rule to complete the formatting criteria. Below are the variable functions available under Conditional Formatting.

 

 

VBA Conditional Formatting - Variable Functions

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

But what if we can automate this process of highlighting duplicate or any kind of values as per our requirement. The criteria which we can define using Conditional Formatting in Excel can also be done in VBA as well. For applying conditional formatting we can choose any cell, range available in Excel worksheet. Conditional formatting works only when the defined criteria meet the requirement. Else, it will not show any color change. With the help of Conditional Formatting in VBA, we can change the color of any cell or cell content, delete the cell color or remove the color as well. Apart from changing the color of the cell, we can change the cell content into Bold text or Italic text. Once done, we can undo all the changes as well.

How to Use Conditional Formatting in Excel VBA?

Below are the different examples to use Conditional Formatting Function in Excel by using VBA code.

You can download this VBA Conditional Formatting Excel Template here – VBA Conditional Formatting Excel Template

VBA Conditional Formatting – Example #1

We have data of some numbers and text as shown below in column A and B. Now we have already categorized the color we need to give to number and text which is in cell D2. We have identified Yellow color for number 1 and alphabet A and Green color for number 2 and alphabet B.

VBA Conditional Formatting - Ex 1

Although VBA Conditional formatting can be implemented in Module but writing the code for Conditional formatting in Sheet will make code work in that sheet only. For this, instead of going to Module option, click on the Insert tab to insert a module.

VBA Conditional Formatting Module

Step 1: Now from the first drop down select Worksheet which will be General by default and from select dropdown it will automatically select the option SelectionChange as shown below.

VBA Conditional Change

Step 2: Once we do that, it will automatically activate the private subcategory and Target cell would be as Range.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Private Sub Worksheet

Step 3: Now write the code firstly define a variable MyRange as Range. Or you can choose any other name instead of MyRange as per your choice.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim MyRange As Range

End Sub

VBA Conditional Formatting Example 3.1

Step 4: Use Set and choose the defined Range as shown below.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 Dim MyRange As Range
 Set MyRange =

End Sub

VBA Conditional Formatting Example 1.2

Step 5: After that select the Worksheet where we want to apply the conditional formatting. Here our sheet is Sheet1. We can put the sequence also as 1 instead of writing Sheet1. And then select the range of those cells which we need to format. Here our range is from cell A1 to B8.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 Dim MyRange As Range
 Set MyRange = Worksheets("Sheet1").Range("A1:B8")

End Sub

VBA Conditional Formatting Example 3.3

Step 6: Now open a For Each-Next loop as shown below. And start that with selecting the Cell defined variable MyRange.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   Dim MyRange As Range
   Set MyRange = Worksheets("Sheet1").Range("A1:B8")

   For Each Cell In MyRange

   Next

End Sub

VBA Conditional Formatting Example 3.4

Step 7: Now in that, again open an If-Else loop.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  Dim MyRange As Range
  Set MyRange = Worksheets("Sheet1").Range("A1:B8")

  For Each Cell In MyRange
  If

  End If
  Next

End Sub

VBA Conditional Formatting Example 3.5

This is the region where we would assign the colors to all numbers and alphabets available in our range.

Step 8: Write the code, if the cell value is 1 then-Interior color the selected range cell which is from A1 to B8 will be Green. And for green, we have color code assigned to it as 6.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  Dim MyRange As Range
  Set MyRange = Worksheets("Sheet1").Range("A1:B8")

  For Each Cell In MyRange
  If Cell.Value Like "1" Then
     Cell.Interior.ColorIndex = 6

  End If
  Next

End Sub

VBA Conditional Formatting Example 3.6

Step 9: Now for cell value number 2. Else if the cell value of any cell from the selected range is 2 then interior color of that cell will be Yellow. And for yellow we have color code assigned to it as 4.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  Dim MyRange As Range
  Set MyRange = Worksheets("Sheet1").Range("A1:B8")

  For Each Cell In MyRange
  If Cell.Value Like "1" Then
     Cell.Interior.ColorIndex = 6
  ElseIf Cell.Value Like "2" Then
     Cell.Interior.ColorIndex = 4

End If
Next

End Sub

VBA Conditional Formatting Example 3.7

For each color, we have different color codes assigned to them which is starting from 1 to 56. Whereas number code 1 is assigned to Black color and number 56 is assigned to Dark grey color. In between, we have different other color shades which we can find from Microsoft Documents.

Step 10: If any of the above

condition is FALSE then we would have another Else if condition where if the cell value is A then interior color of the cell will be Yellow. And for yellow again we will assign code as 6.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 Dim MyRange As Range
 Set MyRange = Worksheets("Sheet1").Range("A1:B8")

 For Each Cell In MyRange
 If Cell.Value Like "1" Then
    Cell.Interior.ColorIndex = 6
 ElseIf Cell.Value Like "2" Then
   Cell.Interior.ColorIndex = 4
 ElseIf Cell.Value Like "A" Then
   Cell.Interior.ColorIndex = 6

 End If
 Next

End Sub

VBA Conditional Formatting Example 3.8

Step 11: Do the same thing for cell value B as well, with color code 4 as Green.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  Dim MyRange As Range
  Set MyRange = Worksheets("Sheet1").Range("A1:B8")

  For Each Cell In MyRange
  If Cell.Value Like "1" Then
     Cell.Interior.ColorIndex = 6
  ElseIf Cell.Value Like "2" Then
     Cell.Interior.ColorIndex = 4
  ElseIf Cell.Value Like "A" Then
     Cell.Interior.ColorIndex = 6
  ElseIf Cell.Value Like "B" Then
     Cell.Interior.ColorIndex = 4

End If
Next

End Sub

VBA Conditional Formatting Example 3.9

Step 12: If any of the condition is not TRUE then for Else we will prefer to select color code as None.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim MyRange As Range
  Set MyRange = Worksheets("Sheet1").Range("A1:B8")

  For Each Cell In MyRange
  If Cell.Value Like "1" Then
Cell.Interior.ColorIndex = 6
  ElseIf Cell.Value Like "2" Then
Cell.Interior.ColorIndex = 4
  ElseIf Cell.Value Like "A" Then
Cell.Interior.ColorIndex = 6
  ElseIf Cell.Value Like "B" Then
Cell.Interior.ColorIndex = 4

  Else
Cell.Ineterios.ColorIndex = xlNone

  End If
  Next

End Sub

VBA Conditional Formatting Example 4.2

Step 13: As the code is big, to compile each step of code press F8 functional key. If no error found then click on the play button to run the entire code in one go. We will see, as per conditional formatting rule defined in VBA code, the color of the cells has been changed to selected color codes as shown below.

Final OutPut of Conditional Formatting

Step 14: This formatting is now fixed. If we want to see the changes in color, for test let’s change the value of any cell consider A1 from 1 to 2. We will see, the color of cell A1 is changed to Green.

After Chnaging the Input

This is because we have declared that in the range A1 to B8, any cell containing numbers 1 and 2 and alphabets A and B, will be formatted as Yellow and Green color as shown in cell D2 to E3.

Pros and Cons

  • It gives instant output if we have huge data. Whereas if we apply the same from the Excel menu option, it will take time to clear the formatting for big data set.
  • We can perform all types of function which are available in Excel for Conditional formatting in VBA as well.
  • It is not recommended to apply VBA Conditional formatting for a small set of data.

Things to Remember

  • There are many other functions apart from highlighting duplicates and same value cells. We can change the format of the cell in any way such as Bold, Italic text, changing the font color, changing the background color, highlighting the values between some specific range.
  • Once conditional formatting applied, we can change the rule, in fact, we can Delete the formatting conditions as well. So that our data will be back to normal.
  • We can apply more than one condition in one macro.

Recommended Articles

This is a guide to VBA Conditional Formatting. Here we discuss how to use Excel VBA Conditional Formatting function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Copy Paste
  2. VBA Get Cell Value
  3. VBA Subscript out of Range
  4. VBA RGB