VBA Worksheet change - incorporate cell with formula

Hi

I have been trying to add to the below, without much success. What I want to do is generate a message if the value in Q12 is greater than the value of S12. Cell Q12 contains a formula. I have to do this on a couple more cells which is why i am unable to use worksheet calculate. i just have not been able to work out how to add it in.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("I7")) Is Nothing Then
    If Target.Value > Sheet1.Range("B36") Then
    MsgBox "XXXXXXXXXXXXX!"
    End If
    ElseIf Not Intersect(Target, Range("E12")) Is Nothing Then
    If Target.Value > Sheet1.Range("C36") Then
    MsgBox "Message here!"
    End If
   
    End If



   End Sub

Hi Jeff,

Thanks for your reply.

You are right I am probably trying to overcomplicate the issue.

Thankyou for pointing out the change event will only trigger if the calculation is on the same worksheet, which does make sense seeing as it is a worksheet change! So with this in mind I have moved the calculations so they are on the same sheet, what I now need to do is only have the message box appear if that particular cell changes, which is why I believe I cannot use the worksheet calculate as I have more than one?

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Range("T7").Value < Range("I7").Value Then
    Sheet7.Unprotect "XXXXX"
    MsgBox "Message 1"
    Sheet7.Protect "XXX"
    End If
    
If Range("E12").Value > Range("E14").Value Then
    Sheet7.Unprotect "XXXX"
    MsgBox "Message 2"
    Sheet7.Protect "XXXX1"
    End If
    
If Range("Q12").Value > Range("S12").Value Then
    Sheet7.Unprotect "XXXX"
    MsgBox "Message 3"
    Sheet7.Protect "XXX"
    End If

Bump - Is anyone able to offer some help, would be much appreciated.

Dear both thanks for helping me with this.

I have attached the file.

The code does work but I am receiving the message when other cells are updated. i.e if you change E12 I receive message 1 and message 2 (message 1 is only related to cell I7)

hope that makes sense.

Hi Winon,

Sorry to confuse you, hopefully the below makes sense?

I have added the code into the worksheet and "message 1" appears if E7 changes and is less than I7, which
is correct. The problem is if I type in T7 then I get message 1 appear again. I would like "message 1" only to appear based on cell E7, "Message 2" only based on data in E12 and "Message 3" only on Q12.

Really appreciate your help and guidance.

Private Sub Worksheet_Change(ByVal Target As Range)
    
If Not Range("T7").Value < Range("I7").Value Then
    MsgBox "Message 1"
End If
    
If Range("E12").Value > Range("E14").Value Then
    MsgBox "Message 2"
End If
    
If Range("Q12").Value > Range("S12").Value Then
    MsgBox "Message 3"
End If
End Sub

Hi Winon,

It is closer but how can I stop message 1 showing up when changes are made in cell E12? I do not want to see message 1 again when I click other cells.

Cell i7 message 1
Cell E12 message 2

Also you changed the cell reference from i7 to E7, think that was in error?

Sorry for confusing you.

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("I7").Value < Range("T7").Value Then
    MsgBox "Message 1"
End If
    
If Range("E12").Value > Range("E14").Value Then
    MsgBox "Message 2"
End If
    
If Range("Q12").Value > Range("S12").Value Then
    MsgBox "Message 3"
End If
End Sub

Yes that is it, Winon you are amazing!

I cannot thank you enough for your help, perseverance and patience.

thank you so much.