It all depends upon what you want to trigger the check. If it is to be data
in the Tracking sheet, C4:C100, then it should be

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ans
If Target.Address = "C4:100" Then
If WorksheetFunction.CountIf(Range("C4:C100"), Range("Sale!P2"))
0 Then
ans = MsgBox("Continue", vbYesNo)
If ans = vbYes Then Application.Run (SortTracker)
End If
End If

End Sub

If it is to be a change to P2 on tghe Sale sheet it should be


Private Sub Worksheet_Change(ByVal Target As Range)
Dim ans
If Target.Address = "$P$2" Then
If WorksheetFunction.CountIf(Range("Tracking!C4:C100" ),
Range("P2")) 0 Then
ans = MsgBox("Continue", vbYesNo)
If ans = vbYes Then Application.Run (SortTracker)
End If
End If

End Sub


You cannot have a cell on one sheet triggering an event on another. And you
must put it in the worksheet nmodule.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Choice" wrote in message
...
Still not working, this is exactly what i have in there. when i go to
Sale!P2...type in a number nothing happens

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ans
If Target.Address = "sale!$P$2" Then
If WorksheetFunction.CountIf(Range("Tracking!C4:C100" ),
Range("Sale!P2")) 0 Then

ans = MsgBox("Continue", vbYesNo)
If ans = vbYes Then Application.Run (SortTracker)
End If
End If

End Sub

when i type a number into sale!p2 and hit enter, i want it to check to see if that number is in tracking!C4:C1000. if its not then i want the message to come up

This should work


Private Sub Worksheet_Change(ByVal Target As Range)
Dim ans
If Target.Address = "$P$2" Then
If WorksheetFunction.CountIf(Worksheets("Tracking"). _
Range("C4:C100"), Range("P2").Value) 0 Then
ans = MsgBox("Continue", vbYesNo)
If ans = vbYes Then SortTracker
End If
End If

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"choice" wrote in message
...
when i type a number into sale!p2 and hit enter, i want it to check to see
if that number is in tracking!C4:C1000. if its not then i want the message
to come up