How to Highlight Duplicates in Excel VBA with a Macro

VBA & macros · Updated November 18, 2026

To highlight duplicate values with VBA, select the cells you want to check, run a macro that counts each nonblank value, and apply a fill color to every value that appears more than once. The steps below are for Excel for Microsoft 365 on Windows; the macro also works in desktop Excel for Mac. Save a copy of your workbook first, because macro changes cannot be undone with Ctrl+Z.

The example highlights all occurrences of a duplicate, including the first one, using a light-yellow fill. It compares text without regard to capitalization, treats numbers as numbers, ignores blank cells and formula results that are empty, and skips cells containing errors. It does not remove duplicate records or change cell values.

What You’ll Need

  • A desktop version of Excel for Microsoft 365. Excel for the web can store a workbook containing VBA, but it cannot create or run VBA macros.
  • A workbook with the values you want to check. Save a backup copy before running the macro.
  • A macro-enabled workbook saved as .xlsm if you want to keep the macro in the file.
  • Permission to run macros. On a work or school computer, your organization may restrict them.

Step-by-Step

  1. Choose the range to check. Select the cells you want included in the duplicate check, such as A2:A100 for a list of product codes. Include all relevant rows, but avoid selecting an entire worksheet column unless you genuinely need it. Selecting a smaller range makes the macro quicker and prevents unrelated values elsewhere on the sheet from affecting the result. If you select more than one range using Ctrl on Windows, the macro checks the selected cells together.
  2. Save a safety copy. Use File > Save As to make a separate copy before running the code. If the workbook is not already macro-enabled, choose Excel Macro-Enabled Workbook (*.xlsm) as the file type. A macro can overwrite cell formatting, and Ctrl+Z does not reverse changes made by VBA.
  3. Open the Visual Basic Editor. On Windows, press Alt+F11, or select Developer > Visual Basic. If you do not see the Developer tab, go to File > Options > Customize Ribbon, select the Developer checkbox, and choose OK. On Mac, open Developer > Visual Basic; if needed, enable the tab in Excel > Settings > Ribbon & Toolbar. The Mac keyboard shortcut can vary by keyboard, and may require Fn with Option+F11.
  4. Insert a standard module. In the Visual Basic Editor, choose Insert > Module. A blank code pane opens. A standard module is a suitable place for a macro you start manually; you do not need to paste this code into a worksheet’s code window.
  5. Paste the macro. Copy the complete code below into the module. It uses late binding to create a dictionary, so you do not need to add a separate library reference in the editor.
Option Explicit

Sub HighlightDuplicatesInSelection()
    Dim target As Range
    Dim cell As Range
    Dim counts As Object
    Dim value As Variant
    Dim key As String

    ' Make sure the current selection is a cell range.
    If TypeName(Selection) <> "Range" Then
        MsgBox "Select the cells to check, then run the macro."
        Exit Sub
    End If
    Set target = Selection

    ' Count nonblank values; text comparisons ignore capitalization.
    Set counts = CreateObject("Scripting.Dictionary")
    counts.CompareMode = vbTextCompare

    For Each cell In target.Cells
        value = cell.Value2
        If IsError(value) Then GoTo NextCount
        If Len(CStr(value)) = 0 Then GoTo NextCount

        If VarType(value) = vbString Then
            key = "T|" & CStr(value)
        ElseIf VarType(value) = vbBoolean Then
            key = "B|" & CStr(value)
        Else
            key = "N|" & CStr(value)
        End If

        If counts.Exists(key) Then
            counts(key) = counts(key) + 1
        Else
            counts.Add key, 1
        End If
NextCount:
    Next cell

    ' Color every cell whose value occurred more than once.
    For Each cell In target.Cells
        value = cell.Value2
        If IsError(value) Then GoTo NextColor
        If Len(CStr(value)) = 0 Then GoTo NextColor

        If VarType(value) = vbString Then
            key = "T|" & CStr(value)
        ElseIf VarType(value) = vbBoolean Then
            key = "B|" & CStr(value)
        Else
            key = "N|" & CStr(value)
        End If

        If counts.Exists(key) Then
            If counts(key) > 1 Then
                cell.Interior.Color = RGB(255, 235, 156)
            End If
        End If
NextColor:
    Next cell

    MsgBox "Duplicate values are highlighted."
End Sub
  1. Run the macro. Return to the worksheet and make sure the intended cells are still selected. On Windows, press Alt+F8, choose HighlightDuplicatesInSelection, then select Run. You can also use Developer > Macros. On Mac, use Developer > Macros; Option+F8 may open the macro list, depending on your keyboard settings.
  2. Check the highlighted cells. Every nonblank value that appears at least twice in the selection should now have a light-yellow fill. Compare a few marked cells against the list before using the result for sorting, review, or cleanup. The macro only formats cells; it does not delete, move, or correct any data.
  3. Adjust the fill color if you want. In the code, replace RGB(255, 235, 156) with another VBA RGB value. For example, RGB(255, 199, 206) gives a light red-pink fill. Save the workbook after editing. The cell’s existing fill is replaced when the macro colors a duplicate, so choose a range where that change is acceptable.

To understand the comparison, consider a selected range containing North, north, South, and North. All three cells containing North or north are highlighted, because the comparison ignores capitalization. A repeated number such as 25 is counted separately from text containing the characters 25. Cells containing spaces are not blank to this macro, and will be compared as text.

Common Mistakes

Selecting the wrong range: The macro only examines the current selection. If you select a column that includes a heading, the heading is included in the check; if you select only part of your data, duplicates outside that selection are not counted. Select the full intended data range, usually excluding the header row.

Pasting into the wrong editor location: The code belongs in a standard module created with Insert > Module. Putting it into a worksheet event window or ThisWorkbook can make it harder to find and run as a regular macro.

Saving as a macro-free file: If you save the workbook as .xlsx, Excel cannot retain its VBA code. Choose .xlsm to keep the macro, or store the macro in a separate macro-enabled workbook if that better fits your workflow.

Assuming colors are cleared automatically: The code applies a fill to duplicate cells but does not remove fills from cells that were highlighted during an earlier run. If you change the data and rerun the macro, an old highlight can remain on a value that is no longer duplicated. Review that behavior before relying on color as a current, definitive result.

Running a macro you have not reviewed: Only run VBA code you understand or trust. Excel may display a security warning when a workbook opens. Do not lower security settings broadly just to run an unfamiliar file; follow your organization’s guidance where applicable.

Tips

Use a clearly bounded range such as B2:B500 rather than whole columns for a large sheet. This makes the comparison easier to verify and avoids counting headings, notes, or unrelated cells. If duplicates should be checked across several columns as one combined list, select those cells together; values in different selected columns are counted against each other.

If you need to distinguish upper- and lowercase text, this macro’s text comparison is not suitable as written: its dictionary uses vbTextCompare, which treats capitalization as equivalent. Likewise, if leading or trailing spaces should be ignored, clean the source values first or adapt the macro deliberately. The current code does not trim spaces, because removing them silently could make distinct entries appear identical.

For a repeatable workflow, keep the code in a dedicated .xlsm workbook and document the expected input range. You can also add a button from the Developer tab and assign the macro, but keep the selection step in mind: the button acts on whichever range is selected when it is clicked. Test changes on a copy before using them on an important workbook.

Excel’s built-in duplicate formatting may be quicker for a one-time check: Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. VBA is useful when you want a repeatable, customizable action or need to incorporate highlighting into a larger macro process. Conditional formatting updates as values change; this VBA macro applies a one-time fill, so run it again after editing the data and address old fills as needed.

Troubleshooting

The macro is missing from the Alt+F8 list: Confirm that the code is in a standard module, that the procedure is named HighlightDuplicatesInSelection, and that the workbook containing it is open. Check that the file is saved as .xlsm if you expect the code to persist after closing it.

Excel blocks the macro: Check the security message and only enable macros for a file you trust. If the workbook came from the internet, Windows or organizational security policies may block it. Ask your administrator for the approved process rather than changing global security settings. Excel for the web cannot execute VBA; open the file in desktop Excel.

No cells are highlighted: Confirm that repeated values are actually inside the selected range and that the macro ran to its completion message. Check for small differences such as extra spaces, different punctuation, or one value stored as text and another stored as a number. The macro deliberately treats text and numbers as different kinds of values.

The result includes a value you consider different: Text matches ignore capitalization, so entries such as ABC and abc count as duplicates. Spaces are significant, and numeric formatting is not: numbers with different display formats can still have the same underlying value. Review the underlying cell contents and adjust the comparison rules if your definition of a duplicate differs.

Old yellow cells remain: This macro does not clear previous fills, because clearing fills could erase formatting you wanted to keep. On a copy, you can select the relevant range and use Home > Clear > Clear Formats before rerunning, but that removes all formatting in the selection, including number formats and borders. Alternatively, remove only the old fill manually or use a clean copy of the data.

Frequently Asked Questions

Does this macro highlight the first occurrence too?
Yes. It counts all values first, then colors every cell whose count is greater than one. That means the first matching cell and each later occurrence receive the same fill.

Will it delete or change duplicate values?
No. It changes fill formatting only. It does not delete rows, edit cell contents, or rearrange the worksheet. Make a copy before running it because formatting changes made by VBA cannot be undone with Ctrl+Z.

Are blank cells treated as duplicates?
No. Empty cells and formulas that return an empty string are skipped. A cell containing one or more spaces is not considered blank and can be counted as a text value.

Does it distinguish uppercase and lowercase?
No. The macro uses a case-insensitive text comparison, so Oak and oak count as a match. Change the comparison approach if capitalization is meaningful in your data.

Can I use it in Excel for the web?
You can open a macro-enabled workbook in Excel for the web, but VBA macros do not run there. Open the workbook in desktop Excel for Windows or Mac to use this macro.

Can I run it again after changing the list?
Yes. Select the range again and run the macro. It will recalculate duplicates from the current values, but it will not remove fills left from a previous run. Review old formatting or use a fresh copy if you need the visible highlights to reflect only the latest result.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.