In this post, you’ll be learning how to find and highlight duplicate values in Range in your excel spreadsheet using Excel VBA.
How to Find and Highlight Duplicate Values in Range in Excel VBA?
To find and highlight duplicate values in Range, first insert a command button in the page and enter the code.
Code:
Option Explicit Sub Highlight_Duplicates(Values As Range) Dim Cell For Each Cell In Values If WorksheetFunction.CountIf(Values, Cell.Value) > 1 Then Cell.Interior.ColorIndex = 8 End If Next Cell End Sub
​x
18
1
Option Explicit
2
​
3
Sub Highlight_Duplicates(Values As Range)
4
​
5
Dim Cell
6
​
7
For Each Cell In Values
8
​
9
If WorksheetFunction.CountIf(Values, Cell.Value) > 1 Then
10
​
11
Cell.Interior.ColorIndex = 8
12
​
13
End If
14
​
15
Next Cell
16
​
17
End Sub
18
​
Code for the Command button:
Private Sub CommandButton1_Click() Highlight_Duplicates (Sheets("Sheet1").Range("C10:F15")) End Sub
C#
xxxxxxxxxx
1
1
Private Sub CommandButton1_Click()
2
​
3
Highlight_Duplicates (Sheets("Sheet1").Range("C10:F15"))
4
​
5
End Sub
6
​
Now Click the button, the highlighted cells contains the duplicate values.