Workbook_SheetChange not triggered by change from formula in other worksheet

Question

I'm trying to imlpement a code that displays a message when a certain condition is met. In this case, it should happen when Sheet2's A1's value is equal or bigger than 1000. This value, on the other hand, is defined by a formula located in Sheet1. I tried implementing a solution based on this thread: How can I run a VBA code each time a cell get is value changed by a formula?

So I got this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim updatedCell As Range
Set updatedCell = Range("A1")

If Not Application.Intersect(updatedCell, Range("A:A")) Is Nothing Then
    If updatedCell.Value >= 1000 Then
        MsgBox "Something requires attention"
    End If
End If
End Sub

When I change the value of A1 through something from Sheet2, it works, but if for example I define it as =Sheet1!A7, and change Sheet1's A7, nothing happens.

How could I make it work?

Answer 1

Well, the linked thread deals with the problem that you want to find out the cell that is recalculated by the current change. (Anyway, the Dependents method only works for formula on the active sheet so this would not work across sheets). In your case, you already know that you only want to monitor one specific (formula) cell.
So, I'd simply go with this:
Put this code in sheet 1 if you know that Sheet2!A1 only depends on values on sheet1.
Just catch all changes and look at your cell each time:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Worksheets("Table2").Range("A1").Value >= 1000 Then
        MsgBox "Something requires attention"
    End If
End Sub

Make sure that you use Worksheets(...).Range - a blank Range can be the source of sleepless nights of error-hunting, it refers to the worksheet where the code is located if the code is in a worksheet module and to the active sheet if it is in another code module.