Highlight Duplicate Values with Excel VBA

Task: Highlight Duplicate Values in Excel Using VBA

We’ll write a VBA code that highlights duplicate values in a selected range of cells. The idea is to compare each cell’s value with the others in the range and if any duplicates are found, we’ll apply a formatting style (e.g., background color) to those cells.

Step-by-Step Explanation of the Code:

1. Sub Declaration:
   We start by declaring the subroutine (macro) to perform the action. This is the entry point of our VBA code.
2. Range Selection:
   We’ll define the range where we want to find duplicates. For flexibility, the user can select the range of cells, and the code will work on that specific range.
3. Loop Through Each Cell:
   The code will loop through each cell in the selected range. For every cell, it will compare its value with the rest of the cells in the range to find duplicates.
4. Comparison for Duplicates:
   If a duplicate is found, it highlights that cell with a specific color.
5. Clear Previous Formatting:
   Before running the check, it’s good practice to clear any existing formatting (like highlighted cells) to ensure that only the new duplicates are highlighted.

VBA Code:

Sub HighlightDuplicates()
    Dim rng As Range
    Dim cell As Range
    Dim compareCell As Range
    Dim duplicateColor As Long
    Dim firstAddress As String
    ' Define the range to check for duplicates
    Set rng = Application.InputBox("Select the range to check for duplicates:", Type:=8   
    ' Exit if the user cancels the range selection
    If rng Is Nothing Then Exit Sub    
    ' Define the color to highlight duplicates
    duplicateColor = RGB(255, 0, 0)  ' Red color for highlighting duplicates    
    ' Clear previous formatting (in case there are any old highlights)
    rng.FormatConditions.Delete    
    ' Loop through each cell in the range
    For Each cell In rng
        If cell.Value <> "" Then  ' Skip empty cells
            ' Compare the current cell with the other cells in the range
            For Each compareCell In rng
                ' If the value is a duplicate and it's not the same cell, highlight it
                If cell.Value = compareCell.Value And Not cell.Address = compareCell.Address Then
                    cell.Interior.Color = duplicateColor  ' Apply the color to the duplicate
                    compareCell.Interior.Color = duplicateColor  ' Apply the color to the compare cell
                End If
            Next compareCell
        End If
    Next cell
End Sub

Detailed Explanation of the Code:

1. Defining the Range to Check:

• We use the InputBox function to allow the user to select a range. The range is assigned to the variable rng.
• The Type:=8 in the InputBox function ensures that the user selects a range, rather than entering text or a number.

2. Handling Duplicates:

• A nested loop structure is used to compare each cell against every other cell in the range. For every cell (cell), it checks if there is another cell (compareCell) with the same value.
• We use If cell.Value = compareCell.Value to check if the values are the same. cell.Address is used to ensure that the code doesn’t compare the cell with itself (i.e., Not cell.Address = compareCell.Address).

3. Highlighting Duplicates:

• If a duplicate is found, both the original cell (cell) and the compared cell (compareCell) are highlighted by changing their background color using cell.Interior.Color = duplicateColor.

4. Clearing Previous Formatting:

• Before starting the comparison, the FormatConditions.Delete method is used to clear any existing conditional formatting from the range. This ensures that previous highlights do not remain and only current duplicates are highlighted.

5. Exit if No Range is Selected:

• If the user clicks « Cancel » on the InputBox, the macro exits without making any changes.

How to Use This Code:

1. Open Excel and press Alt + F11 to open the VBA editor.
2. Insert a new Module by right-clicking on any existing module in the VBAProject window, choosing Insert, then selecting Module.
3. Copy and Paste the above code into the module.
4. Close the editor and press Alt + F8 to open the « Macro » dialog box.
5. Select the HighlightDuplicates macro and click Run.
6. The macro will prompt you to select a range. Once you do, it will highlight all duplicate values in that range with a red color.

Example:

• Let’s say you have a list of numbers in cells A1:A10:
  1, 2, 3, 2, 5, 6, 1, 8, 9, 1.
• Running the macro will highlight cells A1, A2, A6, and A7 (which contain duplicate values 1 and 2) in red.

Conclusion:

This VBA macro helps identify and highlight duplicate values in a selected range, making it useful for cleaning and analyzing data in Excel. The code uses basic loops and conditional logic to compare cell values and apply formatting when duplicates are detected.