Highlight Duplicate Values in Excel with a Simple Macro (Step-by-Step Guide)

Excel Macros

Jun 04, 2026 2 min read

What You’ll Learn
When to Use It
Step 1: The Basic Macro
Step 2: Making It Smarter
a) Work in Selected Range
b) Choose a Custom Color
c) Highlight Only the Second and Beyond Occurrences
Step 3: Adjustments
Step 4: Fixing Common Issues
Wrap-Up

We’ve all dealt with messy Excel sheets where the same value appears more than once. Whether it’s duplicate names, IDs, or product codes, spotting them manually is painful. I’ve been stuck in that situation too. That’s where a simple macro to highlight duplicates can save hours. In this post, I’ll share the basic version, smarter improvements, and easy adjustments you can use right away.

What You’ll Learn

A simple VBA macro to highlight duplicate values
Smarter upgrades like selecting your range or highlighting with custom colors
Easy adjustments to control what gets marked as a duplicate
Fixing common issues if the macro doesn’t behave as expected

When to Use It

Use this macro when:

You’re checking for duplicate names, IDs, or product codes
You want to visually spot duplicates instead of deleting them
Conditional formatting feels too limiting, and you want more control

Step 1: The Basic Macro

Here’s the simplest version to highlight duplicates in column A:

Vba

Sub HighlightDuplicates()
    Dim Rng As Range
    Dim Cell As Range
    
    Set Rng = Range("A1:A100")
    
    For Each Cell In Rng
        If Application.WorksheetFunction.CountIf(Rng, Cell.Value) > 1 And Cell.Value <> "" Then
            Cell.Interior.Color = vbYellow
        End If
    Next Cell
End Sub

How It Works

Define the range:
Range("A1:A100") is where the macro checks for duplicates.
Loop through each cell:
Excel looks at each cell one by one.
Count matches:
CountIf(Rng, Cell.Value) checks how many times that value appears.
Highlight duplicates:
If the count is more than 1, the cell is highlighted in yellow.

In short: Excel scans your range → finds repeated values → colors them.

Step 2: Making It Smarter

a) Work in Selected Range

Instead of hardcoding A1:A100, let’s make it flexible:

Vba

Sub HighlightDuplicatesInSelection()
    Dim Rng As Range
    Dim Cell As Range
    
    Set Rng = Selection
    
    For Each Cell In Rng
        If Application.WorksheetFunction.CountIf(Rng, Cell.Value) > 1 And Cell.Value <> "" Then
            Cell.Interior.Color = vbYellow
        End If
    Next Cell
End Sub

Just select the cells you want before running the macro.

b) Choose a Custom Color

Yellow is fine, but what if you want red or green?

Vba

Sub HighlightDuplicatesRed()
    Dim Rng As Range
    Dim Cell As Range
    
    Set Rng = Selection
    
    For Each Cell In Rng
        If Application.WorksheetFunction.CountIf(Rng, Cell.Value) > 1 And Cell.Value <> "" Then
            Cell.Interior.Color = RGB(255, 100, 100) ' Light Red
        End If
    Next Cell
End Sub

Change the RGB values for any custom color.

c) Highlight Only the Second and Beyond Occurrences

Sometimes you want to keep the first occurrence normal and only highlight the later duplicates:

Vba

Sub HighlightSecondDuplicates()
    Dim Rng As Range
    Dim Cell As Range
    
    Set Rng = Selection
    
    For Each Cell In Rng
        If Application.WorksheetFunction.CountIf(Range(Rng.Cells(1), Cell), Cell.Value) > 1 And Cell.Value <> "" Then
            Cell.Interior.Color = vbYellow
        End If
    Next Cell
End Sub

This way, the first entry stays untouched, and only the repeats light up.

Step 3: Adjustments

Here are some easy adjustments you can make:

Check another column
Change Range("A1:A100") to your required column (e.g., "B1:B200").
Clear previous highlighting
Add Rng.Interior.ColorIndex = xlNone before the loop to remove old highlights first.
Highlight entire rows instead of cells
Replace Cell.Interior.Color with Cell.EntireRow.Interior.Color.
Change highlight style
Instead of color, you can bold text.

Cell.Font.Bold = True

Step 4: Fixing Common Issues

Macro doesn’t run?
→ Ensure macros are enabled in your Excel.
Nothing is highlighted?
→ Double-check your range selection and make sure there are actual duplicates.
Highlighting too many cells?
→ Use the “highlight second and beyond” version to avoid marking the first occurrence.

Wrap-Up

And that’s it! With just a few lines of VBA, you can make duplicates stand out instantly.

We started with a simple macro, then made it smarter by letting you select ranges, change colors, and highlight only repeat entries. Finally, we saw small adjustments to customize it further.

Try this on your data, and you’ll never struggle with spotting duplicates again.

Table of Contents

Related Topics

Learn Excel for free

Helpful Tools

Popular in Excel Macros

Quick Tip

Highlight Duplicate Values in Excel with a Simple Macro (Step-by-Step Guide)

Table of Contents

What You’ll Learn

When to Use It

Step 1: The Basic Macro

Step 2: Making It Smarter

a) Work in Selected Range

b) Choose a Custom Color

c) Highlight Only the Second and Beyond Occurrences

Step 3: Adjustments

Step 4: Fixing Common Issues

Wrap-Up

Comments (0)

Leave a Comment

Related Topics

Helpful Tools

Learn Excel for free

Quick Tip

Table of Contents

Related Topics

Learn Excel for free

Helpful Tools

Popular in Excel Macros

Quick Tip

What You’ll Learn

When to Use It

Step 1: The Basic Macro

Step 2: Making It Smarter

a) Work in Selected Range

b) Choose a Custom Color

c) Highlight Only the Second and Beyond Occurrences

Step 3: Adjustments

Step 4: Fixing Common Issues

Wrap-Up

Comments (0)

Leave a Comment

Related Posts

Excel Macros

Delete Blank Rows in Excel with a Simple Macro (Step-by-Step Guide)

Assign Macros in Excel: A Friendly Guide

Editing VBA Code in Excel: A Beginner’s Tutorial

Step-by-Step Guide: How to Record Macros in Excel

How to Enable and Disable Macros in Excel (Beginner-Friendly Guide)

Related Topics

Helpful Tools

Learn Excel for free

Quick Tip