Studies show that good grammar leads to more promotions and higher salaries. Write with Grammarly

How to Find and Highlight Duplicate Values in Range in Excel VBA?

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.

How to Find and Highlight Duplicate Values in Range in Excel VBA?

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

Code for the Command  button:

Private Sub CommandButton1_Click()

Highlight_Duplicates (Sheets("Sheet1").Range("C10:F15"))

End Sub

Now Click the button, the highlighted cells contains the duplicate values.

How to Find and Highlight Duplicate Values in Range in Excel VBA?