Message Warning Box/Data Validation

I have a spreadsheet that calculates a total (Cell N75) from several other cells (O2+N79-N77) which is a sum of a range of cells (a72:k72) I need a way to be alerted, such as a message box, when the total of N75 falls below 0 or rises above 96. I've tried several custom formulas (=AND(($O$2+$N$79-$N$77)>=0,($O$2+$N$79-$N$77)<=96) in the data validation field, but none seem to work.
I've tried this in the cells where the data is entered, where they are calculated, and in the final cell, N75.

Anyone have any ideas?

thanks!

You could try an event driven macro working off the calulate event to check the vlue of the cell and generate a message box if outside your range.

Data validation will kick in only if the bad value is the result of user input to the cell with validation.
The custom formula

Please Login or Register  to view this content.

in O2, N79 and N77 should work.

Yes, I tried putting that formula in those cells...didn't work. The data is not entered there, but is calculated and linked from yet other cells.

I found my answer...we ended up putting code in the ThisWorkbook code pane. This let the code work on all the worksheets (18 of them) after I went in and enabled the macros.

I appreciate everyones response to my dilemma!