XL:Sample Macro to Find Duplicate Entries in a Row

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Windows, version 5.0, 5.0c

SUMMARY

The Visual Basic for Applications example in this article finds duplicate cell entries within a column and changes the color of the cell containing the duplicate entry to red.

MORE INFORMATION

In order for the macro to function correctly, you must make sure the following conditions are met:

• The column in which you want to find duplicates must be sorted based on values in that column.

  -and-

• The first cell in the column in which you want to find duplicates must be selected.

Sample Visual Basic Code

Sub FindDups ()
   ScreenUpdating = False
   FirstItem = ActiveCell.Value
   SecondItem = ActiveCell.Offset(1, 0).Value
   Offsetcount = 1
   Do While ActiveCell <> ""
      If FirstItem = SecondItem Then
        ActiveCell.Offset(Offsetcount,0).Interior.Color = RGB(255,0,0)
        Offsetcount = Offsetcount + 1
        SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
      Else
        ActiveCell.Offset(Offsetcount, 0).Select
        FirstItem = ActiveCell.Value
        SecondItem = ActiveCell.Offset(1,0).Value
        Offsetcount = 1
      End If
   Loop
   ScreenUpdating = True
End Sub