Excel VBA Trigger event (eg MsgBox) when value in cell C2 changes. but cell C2 has formula in it of =B2 (B2 is drop down list with lot of dependents

Question

Need to trigger event. Planning to use updated value in C2 to use in a loop to look up in another sheet and pull out data where matching and create recordset or like.

But I think step one is to trigger a change.

I have tried this code in sheet1 code (not "ThisWorkbook" or a module)

        Private Sub Worksheet_Change(ByVal Target As Range)
        Dim KeyCells As Range
    
    ' The variable KeyCells contains the cells that will
        ' cause an alert when they are changed.
        Set KeyCells = Range("C2:C2")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
               Is Nothing Then
    
    ' Display a message when one of the designated cells has been
            ' changed.
            ' Place your code here.
            MsgBox "Cell " & Target.Address & " has changed."

End If
End Sub

which works if I physically type a new value in cell C2. But the thing is there is a formula in cell C2 (formula is =B2). And therefore this is not a value change event and the message does not pop up when B2 (and therefore C2) changes. By the way, B2 is a drop down Project list with load of formula based on it showing the profit/loss of the project selected. So I don't want to change the properties of that list or make it combobox/Active X. Hence I am basing off C2 where C2=B2, all in a new and separate sheet.

Therefore I need some help as my approach could be wrong.

FYI the next bit I would do is replace the message box part of the macro to create and paste a recordset of Ids in Column X of Table 1 (naturally unique Ids already) where Column Y of table 1 = C2 (multiple occurences) ie value derived after above change event.

Appreciate if you can go easy on me. I'm mourning a very close loss right now and brain is foggy. Have done this stuff in the past but it's been a while and need help for a tight deadline.