Saturday, April 17, 2010

Excel Spreadsheet Macro: Highlight Duplicates

This is a simple yet effective macro for your Excel spreadsheet. There are times you need to highlight duplicate data in your worksheet. You could use the duplicates function but that actually ends up deleting everything that is a duplicate. Sometimes you may just want to point out what is a duplicate and not physically delete that data. This macro is what you could use instead.
Sub DupinRed()
Application.ScreenUpdating = False
Rng = Selection.Rows.Count
For i = Rng To 1 Step -1
myCheck = ActiveCell
ActiveCell.Offset(1, 0).Select
For j = 1 To i
If ActiveCell = myCheck Then
Selection.Font.Bold = True
Selection.Font.ColorIndex = 3
End If
ActiveCell.Offset(1, 0).Select
Next j
ActiveCell.Offset(-i, 0).Select
Next i
Application.ScreenUpdating = True
End Sub