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 Macrosshowing Macros button in developer tab
  3. Select findAndHighlightDuplicates macro and click on Run button
    running macro to find duplicates in excel
  4. Duplicates in the selected range will be highlighted in light red colorfinding and highlighting duplicates usnig VBA macro

About the Author

SRINI S

A passionate blogger. Love to share solutions and best practices on wordpress hosting, issues and fixes, excel VBA macros and other apps

Leave a Reply

Your email address will not be published. Required fields are marked *