title: VBA worksheet_change limited to single column doesn't respond to automatic updates
body: <p>I am building a patient database. I have code that checks for changes in a specific column. if data in that column reaches a certain range, i make it send an email. Currently when i manually update the column the program works flawlessly, but when i have a date based formula update it - the macro doesn't seem to recognize it.</p>
  
  <p>What could the problem be?</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column &lt;&gt; 22 Then Exit Sub
  Dim rng As Range
  For Each rng In Range("V1:V14")
     If (rng.Value &lt; 5 And rng.Value &gt; 1) Then
         Call mymacro(rng.Address)
     End If
  Next rng
  End Sub
  
  Private Sub mymacro(theValue As String)
  Dim xOutApp As Object
  Dim xOutMail As Object
  Dim xMailBody As String
  Set xOutApp = CreateObject("Outlook.Application")
  Set xOutMail = xOutApp.CreateItem(0)
  xMailBody = "Hi there" &amp; vbNewLine &amp; vbNewLine &amp; _
            "The patient that is due is in cell: " &amp; theValue
  On Error Resume Next
  With xOutMail
      .To = "email@hotmail.com"
      .CC = ""
      .BCC = ""
      .Subject = "Upcoming Patient"
      .Body = xMailBody
      .Display   'or use .Send
  End With
  On Error GoTo 0
  Set xOutMail = Nothing
  Set xOutApp = Nothing
  End Sub
  </code></pre>
  
  <p>the formula that updates the column is</p>
  
  <pre><code>=IF(P7&lt;&gt;"",(P7-TODAY()),"")
  </code></pre>