Hi CJ.
Right click on your worksheet tab, choose "View Code", and paste this in as is.
See if it does what you wish. Checks the usedrange in the column and row of the changed cell.<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, Srng As Range
On Error GoTo DealWithIt
Set rng = UsedRange.Columns(Target.Column)
Set Srng = rng.Find(Target, Target, xlValues, xlWhole, , , False)
If Not Srng Is Nothing And _
Srng.Address<> Target.Address And _
Trim(Target)<> "" Then FoundDuplicate Target, Srng
Set rng = Range(Cells(Target.Row, 1), _
Cells(Target.Row, UsedRange.SpecialCells(xlCellTypeLastCell).Column))
Debug.Print rng.Address
Set Srng = rng.Find(Target, Target, xlValues, xlWhole, , , False)
If Not Srng Is Nothing And _
Srng.Address<> Target.Address And _
Trim(Target)<> "" Then FoundDuplicate Target, Srng
DealWithIt:
End Sub
Private Sub FoundDuplicate(Target As Range, Frng As Range)
Application.EnableEvents = False
If MsgBox("Duplicate entry found in " & Frng.Address & Chr(13) & _
"Would you like to clear your current entry " & _
Target & "?", vbYesNo) = vbYes Then
Target.ClearContents
Target.Select
End If
Application.EnableEvents = True
End Sub</pre>
tom
This message was edited by TsTom on 2002-11-08 11:19