MS Excel how to create a macro to find duplicates and highlight them?

Question

How can I create a macro in MS excel to find duplicates in a spreadsheet and highlight it

Answer 1

You don't need a VBA macro. You can just use conditional formatting. Microsoft explain how to do exactly what you seem to need here:

http://office.microsoft.com/en-us/excel/HA011366161033.aspx

If you really need a macro, the easiest way would be to record the steps described above, then edit as needed.

Answer 2

Maybe this snippet is useful:

Public Sub MarkDuplicates()
Dim iWarnColor As Integer
Dim rng As Range
Dim rngCell As Variant


Set rng = Range("A1:A200") ' area to check '
iWarnColor = xlThemeColorAccent2

For Each rngCell In rng.Cells
    vVal = rngCell.Text
    If (WorksheetFunction.CountIf(rng, vVal) = 1) Then
        rngCell.Interior.Pattern = xlNone
    Else
        rngCell.Interior.ColorIndex = iWarnColor
    End If
Next rngCell
End Sub