Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1113
  • Last Modified:

VBA MsgBox if two cells are not equal to one another

is it possible to put out an error message [box] if two cell do not equal one another.

I would like a message box to look up the word Balance and then if the cell 3 to it's right does not equal the cell 5 to the right of balance issue an error message saying Does not balance. Both cells should contain 0 and the result should be 0

Here's hoping!

Thanks
0
Jagwarman
Asked:
Jagwarman
1 Solution
 
Anthony BerenguelCommented:
Should it look for 'balance' in the entire sheet or a specific column? Can you provide an example workbook?
0
 
Dale FyeCommented:
I would recommend using conditional formatting.
0
 
JagwarmanAuthor Commented:
I prefer to use vba because this is part of a much bigger VBA.

file attached. The Bals at the foot in row 50 should both be 0 which would mean the inputter has input a wrong amount somewhere.

If they insert new rows row 50 will move.

Thanks
Data-the-same-or-not.xlsx
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
byundtCommented:
Here is a macro that will search column K for the word "Balance". It will then compare the cells 3 and 5 columns to the right. If they differ by more than 0.005, then a message is displayed.
Sub TestBalance()
Dim cel As Range
With ActiveSheet
    On Error Resume Next
    Set cel = .Columns("K").Find("Balance", LookAt:=xlPart)
    If Not cel Is Nothing Then
        If Abs(cel.Offset(0, 3).Value - cel.Offset(0, 5).Value) >= 0.005 Then _
            MsgBox "Does not balance"
    End If
    On Error GoTo 0
End With
End Sub

Open in new window

0
 
byundtCommented:
I added a test for whether the balance is zero or not.
Sub TestBalance()
Dim cel As Range
With ActiveSheet
    On Error Resume Next
    Set cel = .Columns("K").Find("Balance", LookAt:=xlPart)
    If Not cel Is Nothing Then
        If Abs(cel.Offset(0, 3).Value - cel.Offset(0, 5).Value) >= 0.005 Then
            MsgBox "Does not balance"
        ElseIf Abs(cel.Offset(0, 3).Value) >= 0.005 Then
            MsgBox "Balance should be zero"
        End If
    End If
    On Error GoTo 0
End With
End Sub

Open in new window

For both tests, I am looking for discrepancies larger than 0.005 to eliminate nuisance warning messages caused by truncation after 15 decimal places (the limit of Excel's accuracy).
0
 
JagwarmanAuthor Commented:
byundt this is exactly what I am looking for. Many thanks. Brilliant
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now