Message box if cell is greater than less than 0

[SG]

I had some pretty good help on this problem and thought we had it nailed,
but a slight problem still remains.

I need a message box to appear when two amounts do not match and I need this
to happen as the amounts are typed in.

To make this short lets say Column A-Cell 1 through Cell 1000 has a negative
number ($50.00) and this will always be the case. Column B-Cell 1 through
Cell1000 will have a matching number with the exception it will be a
positive number. Column C-Cell 1 has the following formula =SUM(A1:B1) and
all Cells below that =SUM(B1:C1) and so on.

The following code whom someone wrote for me works with the exception as
soon as you type in the first number the message box appears.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
Set myRng = Range("C2:X1000") ' Change if necessary
If Application.WorksheetFunction.Sum(myRng) <> 0 Then
MsgBox "Totals are out of Balance"
End If
Set myRng = Nothing
Application.EnableEvents = True
End Sub

Is there a way to make this code pause until both numbers are entered before
it checks it?

Conditional formatting was suggested and turns the Cell a color, but this is
no good for what I need. I really need a message box as I input the Data.
This sheet is quite large and the Cells and Columns run out of site. It was
suggested to do a Window-Freeze, freezing the First 4 or 5 rows, but this
won't work for me. It still causes me to go back and read Check numbers, the
amount entered in the negative number and scroll over to the positive number
and make the correction whichever it may be. I use the Data Form to cut down
on scrolling, but at the end of the day I have to check my last row formulas
to see if all Data was entered correctly and if not it's taking to much time
to correct all my mistakes.

All the best,
SG

 

[Guest]

I'm not quite sure I understand what you are looking for, but have you tried
using data validation? Found under Data-Validation. You can use this to test
a condition upon filling out the cell. If the condition fails a message box
appears.

Regards,

Midnight

 

[Guest]

change your formula to:
=IF(A1<>"",IF(B1<>"",SUM(A1:B1,0),0),0)
This way both cell must be greater than "nothing" before you sum.

 

[SG]

Hi Mike,

Thanks for the response, however this won't work. My fault I didn't explain
as I should have. I was just using A1 B1 C1 as examples.

I always start entering Data in C2, this is under a Column named Bank. After
the Bank Column there are many other Columns with different names such as
Purchases, Phone, Taxes, and so on. At the end of the named Columns whatever
that may be is my formula =SUM(C2:Whatever2) tells me if I entered any
amounts wrong. The problem is it takes to long to scroll through these and
make the corrections my blind eye's and big fumbled fingers make :>)

I really need a message box as I input the Data to tell me of a mismatch.

All the best,
SG

 

[kounoike]

I'm not quite sure this is what you are looking for.
Beside, this uses worksheet event instead of worbook event as you do.
so, put this code not in ThisWorkbook but the Worksheet module data is
populated.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim stcell, endcell
Application.EnableEvents = False
Set stcell = Cells(Target.Row, 2)
On Error Resume Next
Set endcell = Cells(Target.Row, Cells.Columns.Count). _
End(xlToLeft)(1, 0)
If endcell Is Nothing Then
Application.EnableEvents = True
Exit Sub
End If
On Error GoTo ex
If Application.CountBlank(Range(stcell, endcell)) = 0 Then
If Application.WorksheetFunction. _
Sum(Range(stcell, endcell)) <> 0 Then
MsgBox "Totals are out of Balance. " _
& "See Address = " & endcell(1, 2).Address
End If
End If
ex:
Application.EnableEvents = True
End Sub

keizi