VBA macro to find duplicates in excel

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

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