VBA Conditional Formatting Based on Another Cell Value in Excel

When we are dealing with a large amount of data in Excel, sometimes we want to format the dataset in certain conditions for better readability. Implementing VBA is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you 5 effective methods on how we can execute conditional formatting based on another cell value in Excel with the VBA macro.

Apply Conditional Formatting Based on Another Cell Value in Excel with VBA: 5 Methods

In this section, you will learn 5 methods on how to perform conditional formatting with VBA based on another cell value such as text value, numeric value, comparison value etc. in Excel.

1. Embed VBA in Conditional Formatting Based on Comparison Value in Another Cell (maximum 3 conditions)

Look at the following dataset. Here, we have a dataset consisting of Name in Column B, range B5:B12 and Marks in Column C, range C5:C12. We also have a condition (Pass Mark 50) in Cell E5.

What we are going to do is, we will format the cells in the Marks column according to the condition in Cell E5. We will colour the interior of the cells with Blue when the Marks is greater than the Pass Mark 50, with Red when the Marks is less than 50 and with Yellow when the Marks is equal to the condition marks 50.

Let’s see how to do that with VBA code in Excel.

Steps:

• In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• Next, in the pop-up code window, from the menu bar, click Insert -> Module.

• Then, copy the following code and paste it into the code window.

Sub ThreeConditionalFormatting()
Dim iRange As Range
Dim condition1 As FormatCondition
Dim condition2 As FormatCondition
Dim condition3 As FormatCondition
Set iRange = Range("C5", Range("C5").End(xlDown))
'to clear existing conditional formatting
iRange.FormatConditions.Delete
'to specify the condition for each format
Set condition1 = iRange.FormatConditions.Add(xlCellValue, xlGreater, "=$E$5")
Set condition2 = iRange.FormatConditions.Add(xlCellValue, xlLess, "=$E$5")
Set condition3 = iRange.FormatConditions.Add(xlCellValue, xlEqual, "=$E$5")
'to determine the format for each condition
With condition1
.Interior.Color = vbBlue
.Font.Color = vbWhite
End With
With condition2
.Interior.Color = vbRed
.Font.Color = vbWhite
End With
With condition3
.Interior.Color = vbYellow
.Font.Color = vbRed
End With
End Sub

Your code is now ready to run.

• Now, press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro.

After the code execution, look at the image below to see the result.

As a result of the macro, the Marks above the condition (50 in Cell E5) is formatted in the colour blue, under the condition is formatted in the colour red and equal to the condition is coloured in yellow along with changing the font colour.

Read More: Change Font Color Based on Value of Another Cell in Excel

2. Apply VBA to Conditional Format Cells According to Comparison Value in Another Cell (unlimited conditions)

Did you notice one thing in the previous section? Cell C8 is carrying a negative value (-7), which is not valid. But as we were formatting with only 3 conditions, so the macro code calculated the negative value as a value less than 50.

This time we will show you the code where you can apply conditions as many as you like, where you can specify a different output for the negative value as well.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, in the code window, copy the following code and paste it.

Sub UnlimitedConditionalFormatting()
Dim iRange As Range
Set iRange = Range("C5", Range("C5").End(xlDown))
Dim condition As Range
Set condition = Range("E5")
Dim i As Long
Dim iCount As Long
Dim iCell As Range
iCount = iRange.Cells.Count
For i = 1 To iCount
Set iCell = iRange(i)
Select Case iCell
    Case Is > condition
        With iCell
            .Interior.Color = vbBlue
            .Font.Color = vbWhite
        End With
    Case 0 To condition - 1
        With iCell
            .Interior.Color = vbRed
            .Font.Color = vbWhite
        End With
    Case Is = condition
        With iCell
            .Interior.Color = vbYellow
            .Font.Color = vbRed
        End With
    Case Is < 0
        With iCell
        .Value = "Negative Value Error"
        .Interior.Color = vbBlack
        .Font.Color = vbWhite
        End With
   End Select
Next i
End Sub

Your code is now ready to run.

• Now, run the macro and see the image below.

Finally, we can get different outputs for cell value that has a negative number. For instance, we get a “Negative Value Error” message in Cell C8 which used to hold -7. And the rest of the cells are formatted with colors based on the condition in Cell E5 (we discussed this in the previous section).

Read More: How to Copy Conditional Formatting to Another Cell in Excel

3. Implement VBA Macro to Format Cells Based on Text Values from Another Cell in Excel

Look at the following dataset. It has some Colour names in Column B, range B5:B12 and some random Numbers in Column C, range C5:C12.

What we are going to do is, we will colour the numbers in the Number column according to the colour name right next to it from the Colour column.

Steps:

• As shown above, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, copy the following code and paste it into the code window.

Sub ConditionalFormattingTextValue()
'to create variables to store the number of rows
Dim iRow As Long
Dim iValue As Long
'to hold the number of rows
iRow = ActiveSheet.UsedRange.Rows.Count
'to iterate through all the rows in the range
For iValue = 1 To iRow
'initiate a Select Case statement to assess the formatting based on column 2
Select Case ActiveSheet.Cells(iValue, 2).Value
    'to format the interior color to blue and font color to white
    Case "Blue"
    ActiveSheet.Cells(iValue, 3).Interior.Color = vbBlue
    ActiveSheet.Cells(iValue, 3).Font.Color = vbWhite
    'to format the interior color to red and font color to white
    Case "Red"
    ActiveSheet.Cells(iValue, 3).Interior.Color = vbRed
    ActiveSheet.Cells(iValue, 3).Font.Color = vbWhite
    'to format the interior color to green
    Case "Green"
    ActiveSheet.Cells(iValue, 3).Interior.Color = vbGreen
    'to format the interior color to black
    Case "Black"
    ActiveSheet.Cells(iValue, 3).Interior.Color = vbBlack
    ActiveSheet.Cells(iValue, 3).Font.Color = vbWhite
    'to format the interior color to yellow
    Case "Yellow"
    ActiveSheet.Cells(iValue, 3).Interior.Color = vbYellow
    'to format the interior color to cyan
    Case "Cyan"
    ActiveSheet.Cells(iValue, 3).Interior.Color = vbCyan
    'to format the interior color to magenta
    Case "Magenta"
    ActiveSheet.Cells(iValue, 3).Interior.Color = vbMagenta
    'to format the interior color to white
    Case "White"
    ActiveSheet.Cells(iValue, 3).Interior.Color = vbWhite
    End Select
Next iValue
End Sub

Your code is now ready to run.

• Now, run the code and look at the produced result in the picture below.

As a result, the numbers in the Number column are formatted with the colour mentioned in the cell next to them from the Colour column.

Related Content: Conditional Formatting Based on Date in Another Cell in Excel

4. Embed Macro in Conditional Formatting Based on Numeric Values from Another Cell in Excel

With the dataset shown below, we will format in a way that when the Marks are greater than 79, only the font colour will be blue, and when the Marks are less than 40 then the font colour will be red. The rest of the cells will be as they are.

The steps to get that result are given below.

Steps:

• First, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Second, copy the following code and paste it into the code window.

Sub ConditionalFormattingNumValue()
  'to define the variables
  Dim iRange As Range
  Dim iCond1 As FormatCondition
  Dim iCond2 As FormatCondition
 'to set the range on which conditional formatting will be applied
  Set iRange = Range("C5", "C13")
  'to clear any existing conditional formatting
   iRange.FormatConditions.Delete
  'to specify the condition for each format
   Set iCond1 = iRange.FormatConditions.Add(xlCellValue, xlGreater, "=79")
   Set iCond2 = iRange.FormatConditions.Add(xlCellValue, xlLess, "=40")
   'to determine the format for each condition
   With iCond1
    .Font.Color = vbBlue
    .Font.Bold = True
   End With
   With iCond2
     .Font.Color = vbRed
      .Font.Bold = True
   End With
End Sub

Your code is now ready to run.

• Later, run this piece of code. For the result, look at the following image.

As a result, Marks greater than 79 are coloured in blue, less than 40 are coloured in red and the rest are just as they were before.

5. Apply VBA Macro to Conditional Format Cells Referencing to Specific Text Value of Cells

In the following dataset, we have Name, Marks and Position in Columns B, C and D.

This time, we will separate only the cells that hold the word “Top” with the font colour blue and bold.

Steps:

• Firstly, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, copy the following code and paste it into the code window.

Sub ConditionalFormattingSpecificText()
With Range("D5:D13").FormatConditions.Add(xlTextString, TextOperator:=xlContains, String:="Top")
With .Font
.Bold = True
.Color = vbBlue
End With
End With
End Sub

Your code is now ready to run.

• Then, run the macro.

As a result, the font colour of the word “Top” is colored with blue and bold.

Download Workbook

You can download the free practice Excel workbook from here.

Conclusion

To conclude, this article showed you 5 different methods on how we can perform conditional formatting based on another cell value in Excel with the VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.

Related Articles

• How to Do Conditional Formatting Based on Another Cell in Excel
  
• How to Apply Conditional Formatting to the Selected Cells in Excel
• Conditional Formatting Based on Multiple Values of Another Cell
• Conditional Formatting Based On Another Cell Range in Excel