Failing to trigger a message box when cell value in a range is less than 0

[BillyGoat123]

Hi folks,

I'm trying to make a message box pop up with a warning message/instruction when my stock level goes into negative stock, and it has been suggested VBA is the way forward.

I have had success using:

Private Sub Worksheet_Calculate()

If Range("L3").Value < 0 Then _
MsgBox "Negative Stock Alert!" & vbLf &
" - Order more stock"
End Sub

- where L3 is a cell containing a formula to display the stock level.

This works but only for the corresponding cell (L3), if i change it to L5 or L127 etc, it only triggers the message box for "errors" that cell, i.e. if the value is <0.

I attempted to change the cell to a cell range ("L3:L269") as you would normally write it in an Excel formula, but then I get an error window with "Run-time error '13':
Type mismatch"


I've attached my workbook, the column/cell range I'm trying to apply this to is column "L" (Stock Remaining) in the Jobs tab.
I'm very new to this and am a bit confused by all this new language, any advice will be gratefully received.