title: How can I run my Excel macro when a specific cell changes? [new twist]
body: <p>I googled around, and I wrote the following code that I want to run only when a specific cell, D4, changes:</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
      Dim KeyCells As Range
      Static EmailSent As Boolean
      Dim Threshold As Integer
      Dim Cell As String, Email As String, Msg As String
  
      Cell = "D4"
      Threshold = 100
      Email = Range("E7").Value
  
  
      Set KeyCells = Range(Cell)
  
      If Not Application.Intersect(Range(Cell), Range(Target.Address)) Is Nothing Then
          Dim x As Integer
          x = Range(Cell).Value
  
          If x &gt;= Threshold Then
              EmailSent = False
          ElseIf x &lt; Threshold And Not EmailSent Then
              EmailSent = True
              Msg = "You only have " &amp; x &amp; " widgets remaining."
              MsgBox Msg
              SendMail Email, Msg
          End If
      End If
  End Sub
  </code></pre>
  
  <p>This works, and I know there are a lot of similar questions here. <strong>But here's where I'm having trouble:</strong> this only works if I set D4 to an explicit value, say <code>"48"</code>. I <strong>want</strong> it to work even if D4 is a formula: so if D4 is <code>"=SUM(A4:C4)"</code> then an email should send if that sum drops below 100. This code won't send an email in that case :-(</p>
  
  <p>Does anyone know how to fix this?</p>