Unique value in a cell

[Rozallyn]

Hi,

how do I ensure within a range of cells in a worksheet only a unique value can be entered even when pasting into the cell. The data validation function works well for this if the data is typed in but not pasted. Is there a VBA function that I can use?
your help will be greatly appreciated

 

[footoo]

Do you mean prevent duplicates being entered in a certain range ?

 

[Rozallyn]

Yes, thats exactly what I meant....thank you

 

[footoo]

To prevent duplicates in column A :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, r As Range, c As Range
Set rng = [A:A]
Set r = Intersect(rng, Target)
If Not r Is Nothing Then
    For Each c In r
        If WorksheetFunction.CountIf(rng, c) > 1 Then
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
            Exit For
        End If
    Next
End If
End Sub

 

[Rozallyn]

Super star!!!! Thank you so much for your help