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