Conditional formatting over multiple sheets

[BillyGoat123]

Hi Folks!

I have a parts inventory that I am trying to manage in Excel and I want to set up a warning to stop me going into negative stock on any given line item if I try to issue more units than I have in stock; ideally by changing the colour of a cell, or having a pop up warning appear through some sort of data validation setting.

I have one sheet called Stock with the following columns
Stock # (serial index number relating to a given part in the inventory)
typing the Stock # autofills the rest of the line with item information from another catalogue sheet in the workbook.
Received Qty (displayed using a SUMIF function),
Issued Qty(displayed using a different SUMIF function),
Stock Level (displayed using =Received Qty-Issued Qty)

I have another sheet called Job where I type in the Stock # of a part I need in one column, and how many pieces of that part are needed in another column called Count

What I want to happen is for the cell in the Count column on the Job sheet to turn red if I type in a number that is greater than the Stock Level value in the Stock sheet for the corresponding item, thus causing it to go into negative stock.

I have tried to use conditional formatting on the Job sheet to achieve this
using the formula =Stock!$M:$M<0
where Stock! refers to the Stock Sheet,
and M refers to the column containing the Stock Level value,
and setting the "Applies To" range to the Count column on the Job sheet.

I have also tried applying it to one cell =Stock!$M$3<0
and applying it to a range =Stock!$M$3:$M$269<0
I've also tried similar efforts in Highlight Cells Rules but for some reason none of these succeed in highlighting the cell when the stock goes into negative.

What am I missing?
Thanks in advance

[AliGW]

I think you need to create named ranges for CF to work across worksheets.

[Pepe Le Mokko]

Independently of Ali's suggestion ( which is the way to go) the following syntax should be applied for CF ( in general)

I have also tried applying it to one cell =Stock!$M$3<0
and applying it to a range =Stock!$M$3:$M$269<0

Should be

I have also tried applying it to one cell =$M3<0
and applying it to a range =$M$3:$M$269

and a CF like =Stock!$M:$M<0 does not work AFAIK