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.
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.
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.
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.
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.
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
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
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
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
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
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
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
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
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
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
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
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.
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.
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 –
