Macro to alert me when a cell changes value -- popup alert - EXCEL

Question

Hey gurus,

I am looking to create a macro to alert me when a cell's value changes. The cells in this particular column can have values of either "OVER" or "UNDER". I would like to write code to alert me via a popup (Message: "Cell A crosses under(over)") when the value changes.

Thanks,

patrick

Answer 1

You'll want to hook in to the Worksheet_Change event.
Something like this should get you started:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value = "OVER" Then
        MsgBox "Cell " & Target.Address & " crosses OVER"
    ElseIf Target.Value = "UNDER" Then
        MsgBox "Cell " & Target.Address & " crosses UNDER"
    End If
End Sub

Sub just_testing()
    Dim mycell As Object
    Set mycell = ActiveSheet.Cells(1, 1)
    Call Worksheet_Change(mycell)
End Sub

With this code, changing cell A1's value to OVER (case-sensitive!) will print the message "Cell $A$1 crosses OVER".

---

Edit based on further information provided by OP:
For automatic worksheet changes, you'll need to use the Worksheet_Calculate event (example below). And for some strange reason, Excel seems to require that you have =NOW() anywhere in the worksheet for Worksheet_Calculate to actually fire.

Private Sub Worksheet_Calculate()
    Dim mycell As Object
    Set mycell = ActiveSheet.Cells(1, 1)
    If mycell.Value = "OVER" Then
        MsgBox "Cell " & mycell.Address & " crosses OVER"
    ElseIf mycell.Value = "UNDER" Then
        MsgBox "Cell " & mycell.Address & " crosses UNDER"
    End If
End Sub