doublon

Private Sub Workbook_Open()
    Dim lgLig1 As Long
    Dim lgLig2 As Long
    
    ' Feuille à traiter
    With Worksheets("Feuil1")
        ' Boucle de la ligne 3 à 30
        For lgLig1 = 3 To 30
            ' Boucle de la ligne 4 à 30
            For lgLig2 = lglig + 1 To 30
                ' Si doublon, message
                If lgLig1 <> lgLig2 And .Range("A" & lgLig1) <> "" _
                    And .Range("A" & lgLig2) <> "" _
                    And .Range("A" & lgLig1) = .Range("A" & lgLig2) Then
                    
                    MsgBox "Il existe des doublons", vbInformation, "Attention..."
                    Exit Sub
                End If
            Next lgLig2
        Next lgLig1
    End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lgLig As Long
    
    If Not Intersect(Target, Range("A3:A30")) Is Nothing And Target.Count Then
        ' Boucle de la ligne 3 à 30
        For lgLig = 3 To 30
            ' Si doublon, message
            If Range("A" & lgLig) <> "" _
                And Target.Row <> lgLig _
                And Range("A" & lgLig) = Target.Value Then
                        
                MsgBox "Il existe des doublons", vbInformation, "Attention..."
                Exit Sub
            End If
        Next lgLig
    End If
End Sub