Warning when negative number is calcuated across a range of cells

[StuCran]

I have a workbook which calculates stock levels based on a series of calculations. I need a warning box to pop up whenever the "Stock Total" goes negative, as this is something that isn't possible.

I have managed to find a few methods of getting it to work when a value is entered manually, however the "Stock Total" is calculated from cells across different worksheets.

Any help appreciated!

[AliGW]

You can do this without VBA.

Something like in an appropriate empty cell:

=IF(A1<0,"Stock Level Below Zero","")

where A1 is the cell containing the stock level.

It can be adapted to look at the last entry in a range if necessary.

[davsth]

Do you really need a warning box, that could be VBA

Conditional formats to highlight the cell if this happens, format only cells that contain cell value < 0 and choose perhaps a red fill
or/and an if statement =if(cell with total <0,"Error Negative Value","") are simpler to implement

[Richard Buttrey]

Hi,

You'll need a Sheet Calculate event to capture that event. Name the Total stock cell say "TotalStock"

Then create the following macro in the worksheet that contains the Total Stock - assumed to be the 'Sheet1' VBA code name (NOT the sheet tab name) in this code

Please Login or Register  to view this content.

Alternatively without a macro you could put a Conditional format in the Total Stock cell so that it changes to say red when it's <0

[StuCran]

Hi Ali, thanks for the suggestion. Unfortunately I need to apply it to 40+ lines in one table. I have already made the cell obvious there is an error (making it highlight red) but this isn't working well enough to make people look at what's causing the error.

[AliGW]

40+ lines isn't hard - add the formula to the adjacent column and simply drag copy down. It will take seconds.

[StuCran]

Hi Richard, thanks. I have conditionally formatted the cells already to highlight red when they go negative however this doesn't stop the values being left there.

Does your solution apply to a single cell? I need it to apply to any cell in a range which goes negative. I tried it and nothing happened.

[AliGW]

this doesn't stop the values being left there

What does this mean?