Hi Little Penny,

You put the Worksheet change event in ThisWorkbook module like following
where the parameter Sh is the worksheet that fired the event. the use with Sh
and palce a dot in front of all the Ranges etc.

Note following code is untested.

Ensure you delete the code from the sheet module.

Private Sub Workbook_SheetChange _
(ByVal Sh As Object, ByVal Target As Range)

Dim MyRow As Long
With Sh 'Line added
If Target.Column < 2 Then Exit Sub
If WorksheetFunction.CountIf(.Range("B:B"), Target.Value) 1 Then
MyRow = WorksheetFunction.Match(Target.Value, .Range("B:B"), 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 With 'Line added
End Sub

--
Regards,

OssieMac