To get the ball rolling you could adapt something along these lines:


VBA Code:
--------------------


Private Sub Worksheet_Change(ByVal Target As Range)
Dim theRange As Range
If Not Intersect(Target, Range("$B$4:$E$14")) Is Nothing And Not IsEmpty(Target) Then
Set theRange = Range("$B$4:$E$14")
If Application.CountIf(theRange, Target.Value) 1 Then
Set c = Target
Do
Set c = theRange.Find(Target.Value, c)
If c.Address < Target.Address Then c.ClearContents
Loop Until c.Address = Target.Address
End If
End If
End Sub
--------------------






Michael;716053 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


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=200469

http://www.thecodecage.com/forumz