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

Therefore, I decided to share a little piece of code with you, on how to find duplicates in an Excel sheet using VBA. VBA stands for Visual Basic for Applications, a language that allows us to communicate with Excel.

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 above image. The highlighting of the text will help you locate the value, instantly.

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

Related: Find Duplicate in Excel using “Conditional Formatting”

Open a new Excel file 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 (white) Window.

Just above the window, you will see two drop down list, choose the first (showing General). In the drop down list, choose Worksheet, which will add a default procedure called Worksheet_SelectionChange.

We will write our duplicate Locator code inside another event called Worksheet_Change. You can either change it by overwriting the above-mentioned event or choose the event from the second drop down list in the same window.

The blank event will look like this.

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

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 VALUE(S) IN THE SPECIFIED.
        If Application.Evaluate("COUNTIF(" & myDataRng.Address & "," & cell.Address & ")") > 1 Then
            cell.Offset(0, 0).Font.Color = vbRed        ' CHANGE COLOR TO RED.
        End If
    Next cell
     
    Set myDataRng = Nothing
ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

You need to save the file in a Macro Enabled Workbook. To do this, click Save as, choose Excel Macro-Enabled Workbook, and save it.

Also Read: Split Cell Values with Carriage Returns to Multiple Columns using VBA Macro

The Worksheet_Change event will trigger the search and highlight procedure when a user types in or pastes a value inside a cell in the second column, and leaves the cell once the entry is complete. As long as the focus is inside the active cell, it will not trigger the event.

The second column in our example shows various categories of Books. We must add unique values in the category column. Therefore, it is useful for us to write the macro, which will keep an eye on every entry we make.

I have tried to keep the example code simple. The objective is to provide a simple solution to not only programmers, but every excel operator who is looking for a solution to find duplicate values in an Excel sheet using VBA. You can simply copy and paste the code inside the VBA section of the file and save it in the above-mentioned Excel format.

Thanks for reading. 🙂

← PreviousNext →