Try this Worksheet_Change event code. I named the range which should have no
duplicate values UniqRng in this example.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tgt As Range, xx As Range
For Each tgt In Target
If (Not Intersect(tgt, ActiveSheet.Range("UniqRng")) Is Nothing) And _
(Len(tgt.Value) 0) Then
For Each xx In ActiveSheet.Range("UniqRng")
If xx.Address < tgt.Address Then
If xx.Value = tgt.Value Then
xx.Value = vbNullString
End If
End If
Next xx
End If
Next tgt
End Sub


This code should be placed on the code page of the worksheet where you want
this to work.

Hope this helps,

Hutch

"Michael" wrote:

I am looking for a way to limit a named range to unique values. So
that if a value is entered that already exists in the range the value
that already exists would become empty and the new location would hold
the unique value.

I got something sort of working with the selection change event but it
took too long to cycle through each cell in the range to make sure it
did not equal the target value. was not pretty. The range is only
like 50 cells.

I am looking for advise on how to make it work.

Thanks
.