Comparing two cell values

[Moonbeam111]

I have two cells I want to compare F47 and G47. Each has a numerical number through a formula. I'm trying to make it so that if F47 changes values due to a macro (or for any reason really) then if F47 becomes greater than or less than G47 (when before it wasn't) that a messagebox pops up saying "F47" is now greater than/less than "G47". It seems pretty simple but I cant get anything I try to work. Please advice.

 

[Strithken]

Hi there.

Assuming Application.EnableEvents is equal to True when the formulas in "F47" and "G47" are calculated...

This isn't the most elegant solution, but it gets the job done. Let me know if there are any questions.

Add the following VBA code to the Worksheet object where "F47" and "G47" reside:

Private f47_value As Variant
Private g47_value As Variant
Private compare_value As CompareValue
Private Enum CompareValue
    LessThan = -1
    EqualTo = 0
    GreaterThan = 1
End Enum

Private Sub Worksheet_Calculate()
    Dim old_compare_value As CompareValue
    old_compare_value = compare_value
    Call Set_Values
    If compare_value = GreaterThan And compare_value > old_compare_value Then
        MsgBox """F47"" is now greater than ""G47"""
    ElseIf compare_value = LessThan And compare_value < old_compare_value Then
        MsgBox """F47"" is now less than ""G47"""
    End If
End Sub
Public Sub Set_Values()
    f47_value = Me.Range("F47").Value
    g47_value = Me.Range("G47").Value
    If f47_value > g47_value Then
        compare_value = GreaterThan
    ElseIf f47_value < g47_value Then
        compare_value = LessThan
    Else
        compare_value = EqualTo
    End If
End Sub

Add the following VBA code to the Workbook object (replace Sheet1 with the code name for your Worksheet object where "F47" and "G47" reside):

Option Explicit

Private Sub Workbook_Open()
    Call Sheet1.Set_Values
End Sub

 

[Moonbeam111]

Hey Strithken. This code works great! Thank you so much for taking the time to type it. I just have one problem/question. How would I get this code to work on a worksheet that has a space in its name. I get an error "expected end of statement" when I try to call it from the Private Sub Workbook_Open sub procedure. Which I assume is because of the space. I have tried putting quotes around the name but then I get an issue: "expected identifier".

 

[Strithken]

You’ll need to use the Worksheet’s code name instead of the Worksheet’s name. The code name can be found in the VBA IDE next to the Worksheet object; it’s the value that is not in parentheses.

You can also replace Sheet1 with Worksheets("Sheet1"), using the Worksheet's name instead of the code name.