Highlight Duplicates Value in Excel Using Macros

If you want to know how to highlight duplicate cells as well as want to learn using macros, then in this tutorial I am going to explain all about the same using macros in excel to highlight the duplicates.

Before using this tutorial in your Excel, make sure you enabled the Developer Tab. Following are the steps to follow this process. 

Purpose of this process: to highlight all the cells in a selected range that has duplicate values.

Previously In Macros: How to Convert Selection to Sentence Case Using Macros in Excel?

Here we have Duplicate Column in the workbook;

Highlight Duplicates Value

STEP 1: GO TO DEVELOPER> CODE> VISUAL BASIC

Highlight Duplicates Value1

STEP 2: Now you need to paste in your code and select SAVE. Afterward, close the window too.

Copy The Following Code;

Sub HighlightDuplicates()

Dim cells As Range
Dim cell As Range
Set cells = selection

‘Loop through each cell in your selected range looking for duplicates
For Each cell In cells

‘Highlight with a color of your choice, if that cell has duplicate values
If WorksheetFunction.CountIf(cells, cell.Value) > 1 Then
cell.Interior.ColorIndex = 10
End If
Next cell

End Sub

Highlight Duplicates Value2

STEP 4; Now open the sheet that contains the data by following this procedure:

GO TO DEVELOPER>CODE>MACROS> RUN

Highlight Duplicates Value3

Here is your result;

Highlight Duplicates Value Cover