I am trying to write a script that has the result of only allowing one
value within a range at any time. So if the user puts new value in the
range the range is cleared except for the new value. The below works
but if the user moves the cursor into the range it gets cleared. I
only want to it clear and use the new value if a new value is entered
in it.

use the the Change event creates a recursive problem where it keeps
changing and repeating.

Any other ideas?


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim inputval As Variant
Dim c As Variant

inputval = Target.Value

'If Target.Row = 3 Then
For Each c In Range("testrng")
If c.Address = Target.Address Then
'MsgBox Target.Value
Range("testrng").Clear
Target.Value = inputval
End If
Next c
End Sub