Sub Test()
Dim Plage As Range, Cel As Range
Set Plage = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
For Each Cel In Plage
If Application.CountIf(Range("A1", Cel), Cel.Value) > 1 Then
MsgBox "Valeur : " & Cel.Value & Chr(10) & _
"Ligne :" & Cel.Row
Exit Sub
End If
Next Cel
End Sub