Find and Highlight Duplicate Values in Excel using VBA (macro)

Last updated: 17th April 2024

Writing macros in Excel using VBA is not every ones cup of tea. However, these are useful piece of code, which can make Excel worksheets more dynamic. Often, we come across a situation where we have to deal with duplicates in Excel. Finding duplicate values in an Excel sheet becomes more tedious if it has too many data. Here in this article I am going to show how to find and highlight duplicates in your worksheet using a simple macro.

What this Macro will do?

The macro or the codes primary job is to find duplicate values while typing in the values in a particular column. It will not just find, but also highlight the duplicate text. See the image.

Find duplicate in Excel

Once you delete the duplicate text, it will automatically remove the highlight (color red) and set the default color (color black).

Follow these steps.

Open Excel and click Alt + F11. This shortcut key combination will open a VBA project, where you can write the code.

Open the Project Explorer window. To do this click the "View" menu at top left corner of the VBA project or click Ctrl + r. In the project explorer, find "Sheet1" and double click it. This will open a blank Window.

Just above the window, you will see two drop down list, choose the first (showing "General"). In the drop down list, choose Worksheet. See the below image.

Excel VBA Worksheet

We will write our code within Worksheet_Change event. You can find the "change" event within another dropdown list in your VBA editor.

Excel Worksheet Change Event

The blank event will look like this.

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

The macro to find and highlight duplicate

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 Then Exit Sub             ' IF ITS A HEADER, DO NOTHING.
    
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    
    Dim myDataRng As Range
    Dim cell As Range
     
    ' WE WILL SET THE RANGE (SECOND COLUMN).
    Set myDataRng = Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
     
    For Each cell In myDataRng
        cell.Offset(0, 0).Font.Color = vbBlack          ' DEFAULT COLOR.
    
        ' locate duplicate values and highlight in the given range.
        If Application.Evaluate("COUNTIF(" & myDataRng.Address & "," & cell.Address & ")") > 1 Then
            cell.Offset(0, 0).Font.Color = vbRed        ' if its a dupilate value, change text color to red.
        End If
    Next cell
     
    Set myDataRng = Nothing
ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

The Worksheet_Change event will trigger the "search and highlight" procedure when you type in or paste a value inside a cell in the second column and "leave" the cell. As long as the focus is inside the active cell, it will not trigger the event.

COUNTIF() function

See the COUNTIF() function inside the "for each" loop. It searches for a value in a given range.

Syntax

=COUNTIF(range, criteria)

The function takes two parameters.

1) range: The range of cells where you want to look for a value. This is "required".

2) criteria: The text, number or cell reference (like cell.Address). This is "required".

Application.Evaluate() method

This method returns a number (number of times a text occured within the given range). If the number is "greater than" 1, its a duplicate and so it will highlight the cell.

Finally, save the file in a "Macro Enabled Workbook". Click "Save as", choose "Excel Macro-Enabled Workbook", and save it.

← PreviousNext →