Eviter les doublons

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ctl, i As Long, col As Long
    col = 1 [COLOR="Green"]'numéro de la colonne à contrôler[/COLOR]
    If Not Intersect(Target, Me.Columns(col)) Is Nothing Then
        ctl = Me.Range(Me.Cells(1, col), Me.Cells(Me.Rows.Count, col).End(xlUp))
        If VarType(ctl) = 8204 Then
            For i = 1 To UBound(ctl, 1)
                If Not IsEmpty(Target.Cells(1, 1)) And Target.Cells(1, 1) = ctl(i, 1) And i <> Target.Cells(1, 1).Row Then Exit For
            Next i
            If i <= UBound(ctl, 1) And i <> Target.Cells(1, 1).Row Then Target.Cells(1, 1).Select: MsgBox Target.Cells(1, 1).Value & " existe déjà en ligne " & i: Target.Cells(1, 1).Value = Empty
        End If
    End If
End Sub