Cell warns of excessive value

A

Ascesis

OK, my next problem.

I have a cell which calculates the total cost of 'costs' - obviously.

Now, I would like to know how to integrate a warning thing that wil
let the user know when the cost has exceeded a certain value.

For example, if the total cost currently stands at £1000 and then I ad
£2000 to 'cost' so that the total cost is now £3000, I should get
warning saying that this is too much, because the 'budget' is £2000

I've tried various things with the Data > Validation tool, but with n
success. If anyone can help me, it will be greatly appreciated
 
B

Bernie Deitrick

Ascesis,

Use Conditional Formatting instead. You can set the value at which it changes color to a set value,
or to another cell's value.

Use Format / Conditional Format... select "Cell Value" " is greater than" and enter 2000 or a
cell reference, then set the format to shade the cell red when the value is exceeded.

HTH,
Bernie
MS Excel MVP
 
E

Excelenator

You could place the following in the worksheet calculation event in the
VB editor


Code:
--------------------
Private Sub Worksheet_Calculate()
If Range("Costs").Value > 2000 Then
MsgBox "The budget is only 2000"
End If
End Sub
--------------------


This would give you the message box above if the total surpasses the
limit you set.
 
A

Ascesis

Private Sub Worksheet_Calculate()
If Range("Costs").Value > 2000 Then
MsgBox "The budget is only 2000"
End If
End Sub
Click to expand...

Is there something in this that I will have to change so that it work
on my worksheet?

I get a debug error when I paste this code in, and the code edito
highlights "If Range("Costs").Value > 2000 Then" in yellow. Hoverin
the cursor over this reveals something like, 'Method range o
object_worksheet failed'
 
B

Bernie Deitrick

You have to name a cell Costs .

But note that this code will flash a message everytime you calculate, if the value is over 2000.
Better to limit the effect by using the Change event, and checking to see if the cells being summed
have been changed prior to giving the message.

HTH,
Bernie
MS Excel MVP
 
A

Ascesis

yeah, this isn't going too well.

I think a message box is a superfluity for my work, anyway.

Just interested.


I'll take out one of those thick Excel guides from the library soon
:cool
 
Top