My spreadsheet pulls in some real time data on commodity prices. When a value in a field automatically changes (the formula recalculates the value), I want a message to pop up to alert me to the change and tell me which commodity changed.
The Worksheet_Change does not seem to work since only what is displayed in the cell changes, not the formula itself. I found this macro but have been unable to adapt it to my purposes. My values in column G are dates. The name of the commodity is in column A. So, if the value in G2 changes, I want the message to say: New date of G2 for A2."
Appreciate any ideas about the best way to go about this. Thanks.
Code
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = Range("G2:G23")
If Not Intersect(Target, rng) Is Nothing Then
If Application.WorksheetFunction. _
Average(rng) = 5 Then
MsgBox "The average of " & _
rng.Address & " = 5"
End If
End If
Set rng = Nothing
End Sub
Display More