How to make a message windo pop up with a message if cell value exceeds

[Marq]

Using Excel 2007

What would be the code or formula to make a message window pop up if a value of one cell exceeds the value of another?

For example:

Cell A20 has the value of 35 (the 35 is a total allowable man power number).

Cell A21 is the value of a sum of a range of cells (the range of cells, A3:A18, is where I plug the man power numbers)

I would like to have a window pop up with a message saying "Stop! Over Allocated" when cell A21 exceeds the value of cell A20.


This same formal/code will also have to be applied to multiple columns..not just column A.

The columns A, B & C are for individual craft titles....so as I plug numbers in column A for Craftsman Level 1, and I eventually exceed the value of cell A20 I get a pop up indicating I've overallocatted man power for Craftsman level 1.....I would then go back and of course adjust my numbers not to exceed cell A20.......once corrected, I can then move to column B for Craftsman Level 2 and begin the same process...and so on and so on.

 

[Pretty1996]

Does something like this help?

I pasted it into "This Workbook" in the VBA window.

This is prob not the easiest way of doing it but it does show a message box if the Value in A21 is greater than the value in A20 and highlights it also.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim A21 As Integer
Dim A20 As Integer
A21 = Worksheets("Sheet1").Range("A21")
A20 = Worksheets("Sheet1").Range("A20")
If A21 > A20 Then
    MsgBox "Stop! Over Allocated For A"
    With Worksheets("Sheet1").Range("A21").Interior
    .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End If
If A21 < A20 Then
With Worksheets("Sheet1").Range("A21").Interior
     .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End If
End Sub