title: If Cell in Range Contains Value Then Insert Comment in Adjacent Cell
body: <p>I hope the title clarifies the objective. All of my attempts fail miserably, for example:</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  
      With Range("A1:A10") = "blah"
          Range("A1:A10").Offset(0, 1).AddComment "fee"
          Range("A1:A10").Offset(0, 2).AddComment "fi"
          Range("A1:A10").Offset(0, 3).AddComment "fo"
      End With
  
  End Sub
  </code></pre>
  
  <p>I have also tried this approach:</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  
      For Each cell In Range("A1:A10")
          If cell.Value = "blah" Then
              cell.Value.Offset(0, 1).AddComment "fee"
              cell.Value.Offset(0, 2).AddComment "fi"
              cell.Value.Offset(0, 3).AddComment "fo"
          End If
      Next
  
  End Sub
  </code></pre>
  
  <p>And this:</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  
      With Range(Target.Offset(0, 1).Address).AddComment
          Range(Target).Offset(0, 1).Comment.Visible = False
          Range(Target).Offset(0, 1).Comment.Text Text:="fee"            
      End With
  
  End Sub
  </code></pre>
  
  <p>Note that the code is intended to be an event handler inserted in a particular worksheet. I clearly misunderstand VBA syntax with respect to ranges. Any assistance in making any of these subs work would be most appreciated.</p>
  
  <p>Follow up: Tim's suggestion to use Worksheet_Calculate worked like a charm. I was able to accomplish my objective with this final variation on Tim's code:</p>
  
  <pre><code>Private Sub Worksheet_Calculate()
  
      Dim rng As Range, cell As Range
  
      'see if any changes are in the monitored range...
      Set rng = Range("A1:A10")
  
      If Not rng Is Nothing Then
  
          For Each cell In rng.Cells
              If cell.Value = "blah" Then
                  cell.Offset(0, 1).AddComment "fee"
                  cell.Offset(0, 2).AddComment "fi"
                  cell.Offset(0, 3).AddComment "fo"
              End If
          Next
  
      End If
  
  End Sub
  </code></pre>