Message box to pop up when duplicate values are in a column a

**mainroad** · 04-03-2013, 07:12 AM

Hi

I am after a routine to add into a macro that displays a message box if duplicate data is in say - column A.

the important aspect is that I am copying and pasting data from another file into sheet1 - hence data validation doesnt seem to work.

i need an independent macro i can tack on or run independently not a none built into sheet1

thanks for your help.

**AndyLitch** · 04-03-2013, 08:24 AM

Dunno about a popup because you may have multiple duplicates so it could get a bit boring..

The following uses conditional highlighting to show duplicates

Sub HighlightDupes()
Dim LastRow As Long
    Range("A1").Select
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
    Range("$A$1:$A" & LastRow).FormatConditions.Delete
    Range("$A$1:$A" & LastRow).FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF($A2:$A$" & LastRow & ",A1)>0"
    Range("$A$1:$A" & LastRow).FormatConditions(Range("$A$1:$A" & LastRow).FormatConditions.Count).SetFirstPriority
    With Range("$A$1:$A" & LastRow).FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
    Range("$A$1:$A" & LastRow).FormatConditions(1).StopIfTrue = False
End Sub

Note ; If the duplicates are a pair - it only highlights the first instance