На случай копи-паст нескольких значений:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, Arr(), ArrItem, Txt As String, DObj As Object
'
If Intersect(Target, [B:B]) Is Nothing Then Exit Sub
If WorksheetFunction.CountA([B:B]) = 0 Then Exit Sub
If IsEmpty([B1]) Then
Set Rng = Range([B1].End(xlDown), [B65536].End(xlUp))
Else
Set Rng = Range([B1], [B65536].End(xlUp))
End If
If Rng.Cells.Count = 1 Then Exit Sub
Arr = Rng.Value
Set DObj = CreateObject("Scripting.Dictionary")
DObj.CompareMode = 1
For Each ArrItem In Arr
If Not IsEmpty(ArrItem) Then
Txt = ArrItem
If Not DObj.Exists(Txt) Then
DObj.Add Txt, ""
Else
MsgBox "Äåæàâþ. ", vbExclamation, "Îøèáêà:"
With Application
.EnableEvents = False
.Undo
If .CutCopyMode Then .CutCopyMode = 0
.EnableEvents = True
End With
Exit For
End If
End If
Next
Set DObj = Nothing
End Sub