Hi Mike,
Can this be changed to sit in a userform textbox2 exit event which fires
when user exits textbox2? The number is now entered in a userform and
transferred to column 'D' (hopefully after your validation code checks) when
command (close) button is clicked.
Thanks again
--
tia


"Mike H" wrote:

Jock, try this worksheet code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRow As Long
If Target.Column < 4 Then Exit Sub
If WorksheetFunction.CountIf(Range("D:D"), Target.Value) 1 Then
MyRow = WorksheetFunction.Match(Target.Value, Range("D:D"), 0)
response = MsgBox("That number has been used on row " _
& MyRow & " Yes to continue NO to cancel", vbYesNo, "Warning")
If response = vbYes Then
Exit Sub
Else
Target.ClearContents
End If
End If
End Sub

Mike

"Jock" wrote:

If a user enters a number in column 'D' which has already been used, rather
than simply identifying that the number has been used already, how can I get
a message box to appear asking if they want to continue using that particular
number or not? If they select yes, end sub, if they select no, target.value =
vbnullstring. It is purely a warning.
In this case, dupes are allowed but I want to alert the user that the number
they have entered has already been used therefore eliminating accidental
dupes (hopefully). Can code inform the user that dupe(s) are on row xx?
Thanks,
--
Traa Dy Liooar

Jock