[VBA] Macro to Highlight Duplicate Values in the Selection (Conditional Formatting)

Introduction

When dealing with customer lists or product code tables, there are frequent situations where you want to check for duplicate data. Searching manually is difficult, but by using Excel’s “Conditional Formatting,” you can automatically highlight cells that contain duplicate values.

By registering this useful feature as a VBA macro, you can execute it at any time with a single click. In this article, I will introduce a simple and practical macro that colors duplicate values within a selected cell range.

VBA Sample Code to Color Duplicate Cells

This macro operates according to the following steps:

1. First, it clears all existing conditional formatting from the selected range.
2. Next, it adds a new formatting rule specifically for “Duplicate Values.”
3. It changes the background color of the cells that match the condition (cells with duplicate values) to a specified color.

Complete Code

' Change the background color of cells with duplicate values within the selected range
Sub HighlightDuplicateValues()

    '== Define variables ==
    Dim targetRange As Range
    
    ' Check if the selection is a cell range (to account for selected charts, etc.)
    If TypeName(Selection) <> "Range" Then
        MsgBox "Please select a cell range before running the macro.", vbExclamation
        Exit Sub
    End If
    
    ' Set the target range to the current selection
    Set targetRange = Selection
    
    '== Clear existing conditional formatting in the selection ==
    targetRange.FormatConditions.Delete
    
    '== Add conditional formatting to highlight "Duplicate Values" ==
    ' Use .AddUniqueValues to add a rule for duplicate/unique values
    With targetRange.FormatConditions.AddUniqueValues
        ' Specify "Duplicate" using the .DupeUnique property
        .DupeUnique = xlDuplicate
        
        ' Formatting: Set background color to light red
        .Interior.Color = RGB(255, 199, 206)
        ' Formatting: Set font color to dark red
        .Font.Color = RGB(156, 0, 6)
    End With
    
    MsgBox "Duplicate values have been highlighted.", vbInformation

End Sub

Code Explanation

targetRange.FormatConditions.Delete

To allow the macro to be executed multiple times without issues, the .Delete method is used to clear any old conditional formatting from the selected range first. This prevents multiple layers of rules from being applied to the same cells.

targetRange.FormatConditions.AddUniqueValues

This adds a new rule (.AddUniqueValues) to the conditional formatting collection (.FormatConditions) of the selected range (targetRange) to check for duplicate or unique values.

.DupeUnique = xlDuplicate

This setting determines which type of values the rule targets. xlDuplicate signifies “Duplicate Values.” If you wish to color unique values (values that do not have duplicates) instead, you would change this to xlUnique.

.Interior.Color = RGB(255, 199, 206)

This sets the formatting for the cells that match the condition.

• .Interior.Color: Specifies the interior (background) color of the cell.
• RGB(255, 199, 206): This function creates a color by specifying values for the three primary colors of light (Red, Green, Blue). This example reproduces the standard Excel “Light Red Fill with Dark Red Text” style. You can change these values to any color of your choice.

Summary

In this article, I explained a simple and convenient method to color duplicate data in a selection using VBA.

• Use FormatConditions.AddUniqueValues to add a rule for duplicate checking.
• Specify the condition as “Duplicate” using .DupeUnique = xlDuplicate.
• Freely set the highlight format using properties like .Interior.Color.

By saving this macro in your Personal Macro Workbook and adding it to the Quick Access Toolbar, you can significantly improve the efficiency of your data checking tasks. Please make use of it in your daily operations.