vba - 如果范围中的单元格包含值，则在相邻单元格中插入注释

Question

我希望标题能够阐明目标。我所有的尝试都惨遭失败，例如:

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

我也尝试过这种方法:

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

还有这个:

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

请注意，该代码旨在作为插入特定工作表中的事件处理程序。我显然误解了有关范围的 VBA 语法。任何使这些子程序正常工作的帮助将不胜感激。

跟进:蒂姆关于使用 Worksheet_Calculate 的建议非常有效。我能够通过 Tim 代码的最终变体实现我的目标:

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

Answer 1

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range, cell as Range

    On Error Goto haveError

    'see if any changes are in the monitored range...
    Set rng = Application.Intersect(Target, Me.Range("A1:A10"))

    If Not rng is Nothing Then
    'Next line prevents code updates from re-triggering this...
    '  (Not really needed if you're only adding comments)
    Application.EnableEvents=False
    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
    Application.EnableEvents=True

    End If

    Exit Sub

haveError:
    msgbox Err.Description
    Application.EnableEvents=True



End Sub