• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1228
  • 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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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