Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, cel As Range, Zone As Range, prem As String
[A:A].Interior.ColorIndex = xlNone
Set Zone = Range("A1:A" & Range("A65536").End(xlUp).Row)
For Each cel In Zone
Set c = Zone.Find(what:=cel, LookIn:=xlValues, LookAt:=xlWhole)
prem = cel.Address
Do While Not c Is Nothing And c.Address <> prem
c.Interior.ColorIndex = 3
cel.Interior.ColorIndex = 3
Set c = Zone.FindNext(c)
Loop
Next cel
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
[A:A].Interior.ColorIndex = xlNone
Set c = Range("A1")
Do While c <> ""
If Evaluate("Countif(A:A," & c & ")") > 1 Then c.Interior.ColorIndex = 3
Set c = c(2, 1)
Loop
End Sub