Add Conditional Formatting To Unique Values Excel VBA Macro

Conditional formatting is a feature of Microsoft Excel that allows highlighting certain values to easily identify those cells. Excel offers various options for numerical values (greater or less than, between, top 10, data bars, color scales, icons, etc.), but there are less options for text. In both cases, we can set rules manually to apply formatting based on a given condition. This post shows how to apply rules automatically using Excel VBA macros. The macro specifically applies formatting to a column based on unique cell values. As a result, each unique cell value is highlighted in the same color.

Macro code:

 
  Sub AddCondFormatToColumn()
 
      Dim MyCol As New Collection, rng As Range, cell As Range, i As Integer
 
      Set rng = Columns("C")
      rng.FormatConditions.Delete
 
      On Error Resume Next
      For Each cell In rng.SpecialCells(xlCellTypeConstants)
          If cell.Row > 1 Then 'to skip the header
              If MyCol.Item(cell) Is Nothing Then 'to target unique values
                  MyCol.Add cell, cell
                  i = i + 1
                  

                  With rng
                      .FormatConditions.Add xlCellValue, xlEqual, cell.Value
                      .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 two Range object variables to store the target range or column, and each cell while looping through the target range. We also need an integer variable to count the number of unique values in the range, which determines the number of conditional formatting rules.
• Then we define the target range assigned to the variable rng. In the example above, that’s column C, which shows the class or group where each of the persons in the list belongs to. Next, we delete the existing conditional formatting in that range (if any).
• Now we start a loop through each cell with content in the target range. Thus, despite selecting the entire column, we only target the cells with content 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 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 added to the target range as follows: The first line adds a condition of the type xlCellValue, for every cell in the target range that equals the corresponding cell value (Class1, Class2, and Class3). The second line formats the interior color of each cell that meets that condition. 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 the cells in the target range, and the process to add conditional formatting repeats for as many unique values as there are. That create as many conditional formatting rules as unique values in the target range. In the example above, three conditional formatting rules are created.

 

This is how we add conditional formatting to unique values in Excel using VBA macros.

Other examples:

• How to Send Multiple Emails with Excel VBA macros
• How to Convert Range To HTML Table with Excel VBA macros
• How to Export Table To Word with Excel VBA macros