Yes, if the cell stays below 200, you will get a message box everytime there
is a calculation; not only when it changes.

I see I wasn't paying attention when I wrote mine

Private Sub Worksheet_Calculate()
Static OldVal as variant
if not isempty(OldVal) and isnumeric(OldVal) then
if isnumeric(Range("D4")) then
if OldVal = 200 and Range("D4") < 200 then
msgbox "Stock is LOW"
end if
end if
End if
if isnumeric(Range("D4")) then
OldVal = Range("D4").Value
else
oldval = Empty
End if
End Sub


worked for me
Regards,
Tom Ogilvy

"jimE" wrote:

thanks tom
it didnt work for me but i changed it to

Private Sub Worksheet_Calculate()

If Range("D4") < 200 Then
MsgBox "Stock is LOW"
End If
End Sub

the first code was coming up with compile error but got the gist of cell
ref. do u c any problems with using the code above. its for stock control.

thanx again tom
"Tom Ogilvy" wrote:

Private Sub Worksheet_Calculate()
Static v as OldVal
if not isempty(OldVal)
if OldVal = 200 and Range("B9") < 200 then
msgbox "It has happened again"
end if
End if
if isnumeric(Range("B9")) then
OldVal = Range("B9").Value
else
oldval = Empty
End if
End Sub

Right click on the sheet tab where you want this behavior and select view
code,'

Put in code like the above untested pseudo code.

--
Regards,
Tom Ogilvy

"jimE" wrote:

Hi. i dont really kno much vb and so help please.

i want a message to pop up if a cell value falls below 200.

how do i write the code for this.
how do i let vb know what wooksheet and what cell i want to use.

i guess it will be easy for u guys but i just thick

cheers jimE