VBA macro to find duplicates in excel

February 4, 2017

by SRINI S

VBA macro to find duplicates in excel sheet. This macro finds the duplicates in user selected range and highlights them in light red color. Also see VBA macro to remove duplicates

VBA code to find and highlight duplicates

Sub findAndHighlightDuplicates()
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

Add above code to your excel workbook

How to use this macro

Select the range (column/row/columns and rows…) in which you want to find the duplicates
Under Developer tab, click on Macros
Select findAndHighlightDuplicates macro and click on Run button
Duplicates in the selected range will be highlighted in light red color