Pop Up A Message Box If Cells Value In A Column Is Greater Or Less Than Cells Value In Another Column

[mergim]

Hi,

I have some issues doing some VBA Coding. What I am trying to do is, if values in a column is greater than the corresponding values in another column (e.g. A1 and B1), it should show a messagebox. In my scenario it is regarding a date. So fx. if the date values in column a is greater/after/far ahead than values in column b, it should show error, because fx an award date should always be before production starts, as you dont start production before you get awarded. I hope you guys can help! Thank you in advance :;D

 

[Tegg]

Hello,

Just to note, if the cell has an error inside it will cause an error in VBA
For the VBA code, is it ran in a macro or when a cell value is updated?

 

[mergim]

It is when a cell value is changed or updated. People tend to change award date but forget to change production start - so I want it to be a kind of a reminder :D

 

[Tegg]

If you add this into the sheet you are working on, I think this might be what you're looking for
Be aware, if there are multiple cells that are greater than the corresponding column then it will do a message box for each cell
I would recommend changing them all first before adding this code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range

For Each Cell In Range("A:A")
If Not Cell.Value = "" Then
If Cell.Value > Cell.Offset(0, 1).Value Then
    Cell.Select
    MsgBox "Value in Cell " & Cell.Address & " is greater than corresponding column"
End If
End If
Next Cell

End Sub

 

[EXCEL MAX]

In the cell "C1" create virtual calculation. For exaple insert "=(A1*1)"
In the formulas tab turn on calculation option to the " Automatic"
Open code editor and put this code in the corresponding sheet.

Option Explicit

Private Sub Worksheet_Calculate()
    
     If Range("A1").Value > Range("B1").Value Then MsgBox ("ERROR")
     
End Sub

 

[mergim]

That didn't work... What I am trying to do is - If values in column D is greater than values in Column G, then a msgbox should appear. Right now there are no greater value in column D than G. Note that it is the rows that should be compared, so D1 with G1, D2 with G2 and so on... So something like "D:D" and "G:G".
Thank you :D

 

[Trixterz]

That didn't work... What I am trying to do is - If values in column D is greater than values in Column G, then a msgbox should appear. Right now there are no greater value in column D than G. Note that it is the rows that should be compared, so D1 with G1, D2 with G2 and so on... So something like "D:D" and "G:G".
Thank you :D

Try this in your worksheet...

Option Explicit

Private Sub Worksheet_Calculate()
     'Remarks Column D=4 G=7
     If ActiveCell.Column = 4 Then
        If Range("D" & ActiveCell.Row).Value > Range("G" & ActiveCell.Row).Value Then
            MsgBox "Your Message Here..."
        End If
     End If
End Sub

 

[My Aswer Is This]

That didn't work... What I am trying to do is - If values in column D is greater than values in Column G, then a msgbox should appear. Right now there are no greater value in column D than G. Note that it is the rows that should be compared, so D1 with G1, D2 with G2 and so on... So something like "D:D" and "G:G".
Thank you :D

In your original post you said:
another column (e.g. A1 and B1)

 

[mergim]

In your original post you said:
another column (e.g. A1 and B1)

Yeah but it was meant as an example... Sry :D

 

[EXCEL MAX]

If you want to do that for all ranges with data in the column "D",
you can do something like this.

Sub CompareRanges()

    Dim varNRows As Long
    Dim varWorksheetName As Worksheet
    Dim varRange1, varRange2 As Range
    
    Set varWorksheetName = Sheets("YourSheetName")
    varNRows = varWorksheetName.Cells(Rows.Count, "D").End(xlUp).Row
    Set varRange2 = varWorksheetName.Range("D1:D" & varNRows)
    
    For Each varRange1 In varRange2
       If varRange1.Value > varRange1.Offset(0, 3).Value Then
          MsgBox ("ERROR! cell 'D" & varRange1.Row & "'")
          Exit Sub
       End If
    Next

End Sub