code doesnt work
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
|