Highlight Duplicates Excel VBA Macro

This example shows how to highlight duplicates with Excel VBA. It uses a For Each loop to check if any value in a given range selection is repeated across the selection. In that case, it highlights all cells with the same value.

Macro/VBA code:

  

  Sub HighlightDuplicateValues()

      Dim rng As Range, rngCell As Range

      Set rng = Selection

      For Each rngCell In rng

          If WorksheetFunction.CountIf(rng, rngCell.Value) > 1 Then

              rngCell.Interior.Color = vbYellow

          End If

      Next rngCell

  End Sub

 

Macro explained:

• The first step is to determine the range selection by declaring a range object (rng) and then defining it with the 'Set' statement. In this case it takes whatever we manually select on the worksheet (Selection), but this can be changed to a particular range if needed: Set rng = ActiveSheet.Range(“A1:Z500”)
• The 'For Each' loop runs through each cell (rngCell) within the given range (rng). Note that rngCell was declared as a Range object (an individual cell is also a Range object).
• The ‘CountIf’ worksheet function is used to check if the value in each cell is repeated within the range. It takes two parameters: the selected range (rng) and the value of each cell we are looping through (rngCell).
• If that condition is met, it changes the interior color of that particular cell to yellow.

This is how we highlight duplicates in Excel using VBA macros.

Other examples:

• How to Delete Blank Rows with Excel VBA macros
• How to List Unique Entries with Excel VBA macros
• How to Order Sheets Alphabetically with Excel VBA macros