Solved: Compare 2 collums and have a message box display varience

[BENSON]

I wish to compare two collums of data "J4:J181" against "Q4:Q181" in most cases the cell values will be the same,or at least pretty close.However if a value in any of the cells in collum "J" exceeds 10% of the equilent cell value in collum "Q" a message box should appear before close listing, any such variences . The items that the data refers to is in collum "A4:A181" so if for example the value of cell "J4 " is greater by 10% of "Q4" the code i need would look at the item description in collum "A" in this case "ROLLS" and return a message such as

"Check Roll order"

THANKS

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim msg As String

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 4 To 181

If .Cells(i, "J").Value > .Cells(i, "Q").Value * 1.1 Then

msg = msg & .Cells(i, "A").Address & " - " & .Cells(i, "A").Value & vbNewLine
End If
Next i

If msg <> "" Then msg = "Check Roll Order" & vbNewLine & vbNewLine & msg
MsgBox msg

End With

End Sub
[/vba]

[BENSON]

Thanks for the code Xld, I get a "Run TIme Error 13 ,Type Mismatch "as show below:
[VBA]
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim msg As String

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 4 To 181

If .Cells(i, "J").Value > .Cells(i, "Q").Value * 1.1 Then

msg = msg & .Cells(i, "A").Address & " - " & .Cells(i, "A").Value & vbNewLine
End If
Next i

If msg <> "" Then msg = "Check Roll Order" & vbNewLine & vbNewLine & msg
MsgBox msg

End With

End Sub
[/VBA]

[Bob Phillips]

Must be text or errors. Try this variation

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim msg As String

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 4 To 181

If IsNumeric(.Cells(i, "J").Value) And IsNumeric(.Cells(i, "Q").Value) Then

If .Cells(i, "J").Value > .Cells(i, "Q").Value * 1.1 Then

msg = msg & .Cells(i, "A").Address & " - " & .Cells(i, "A").Value & vbNewLine
End If
End If
Next i

If msg <> "" Then

msg = "Check Roll Order" & vbNewLine & vbNewLine & msg
MsgBox msg
End If
End With

End Sub
[/vba]

[mdmackillop]

You could also use Conditional Formatting to highlight variances as the data is entered.

[BENSON]

Perfect many thanks, it was a cell containing text that caused the previous error message