Conditional Formatting For Another Cell Excel VBA Macro

In a previous post we saw how to apply conditional formatting rules in Excel with VBA macros. In that example, formatting was applied to a column based on unique cell values, thus highlighting each unique cell value with the same color. In this post we see how to apply conditional formatting to a column based on the value in another column. We can tweak the code slightly to highlight the entire row as well.

 

Macro code:

 
  Sub HighlightSameValuesCondFormatAnotherCell()
      Dim MyCol As New Collection, valCol As Range, forCol As Range
      Dim valRef As String, cell As Range, i As Integer, cond As String     
    
      Set valCol = Columns("C")
      Set forCol = Columns("A")
 
      forCol.FormatConditions.Delete
      valRef = Cells(1, valCol.Column).Address(False, False)
 
      On Error Resume Next
      For Each cell In valCol.SpecialCells(xlCellTypeConstants)
          If cell.Row > 1 Then
              If MyCol.Item(cell) Is Nothing Then
                  MyCol.Add cell, cell
                  i = i + 1
 
                  cond = "=" & valRef & "=" & Chr(34) & cell.Value & Chr(34)
       
                  With forCol
                      .FormatConditions.Add Type:=xlExpression, Formula1:=cond
                      .FormatConditions(i).Interior.ColorIndex = 34 + i
                  End With
              End If
          End If
      Next cell
  End Sub
 

Macro explained:

• First, we declare/define a new collection that serves to identify unique values in a range or column. We also declare three Range object variables to store the column with values, the target column to be formatted, and each cell while looping through the range. We also need two string variables to store the reference column address (valRef) and the conditional formula (cond), and an integer variable (i) to count the number of unique values in the range (which determines the number of conditional formatting rules).
• Then we define the reference column with values (valCol) and the target column where formatting is applied (forCol). In the example above, valCol is column C, which shows the group or class each person belongs to, and forCol is column A, which has the name of the person.
• Next, we delete the existing conditional formatting in the target column (forCol) if any, and get the address of the column with values into a string variable (valRef).
• Now we start a loop through each cell with content in the reference column using special cells xlCellTypeConstants. Note the error handling statement just before the loop starts. That prevents a run-time error when trying to add the same item to the collection (see later).
• Inside the loop, the first condition checks the row number to ensure we do not format the first row (only needed if the table has headers). The second condition makes sure the macro proceeds only for unique values (in the example above, those are Class1, Class2, and Class3). Unique values are stored in the collection by adding the cell value as both item and key to the collection. The variable i increments by one when the unique value is added.
• For each unique value, conditional formatting is determined by the formula stored in the variable “cond”. Then, the macro adds a condition of the type xlExpression and formats the interior color of each cell that meets that condition (cond). Note that we use the ColorIndex property, which allows to set 56 different colors. The macro starts from color index 35 arbitrarily.
• The loop continues through all cells in the reference column and keeps adding conditional formatting for as many unique values as there are. That creates as many conditional formatting rules as unique values in the column with values. In the example above, three conditional formatting rules are created.

This is how we add conditional formatting based on another column in Excel using VBA macros.

Other examples:

• How to Send Google Mail from Excel with VBA macros
• How to Export Range As Picture with Excel VBA macros
• How to Sort Data With Custom List with Excel VBA macros