vba - 在VBA中获取单元格更改的行号

Question

我知道如何检查列是否已更改，就像这样

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("H5")) Is Nothing Then 
        'Pop up a message saying H5 has changed
    End If
End Sub

如果我想更改同一行的另一列，我可以这样做

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("H5")) Is Nothing Then 
        Range("A5").Value = "Look at me!"
    End If
End Sub

现在，如果我想实现上述目标，但对于 row1 到表末尾范围内的所有列该怎么办？像这样的东西(注意，我知道这行不通)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("H1:H")) Is Nothing Then 
        Range("A" & Target.row).Value = "Look at me!"
    End If
End Sub

Answer 1

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("H:H")) Is Nothing Then 
        Range("A" & Target.row).Value = "Look at me!"
    End If
End Sub

不过您应该注意，如果在同一操作中更新了多个单元格，则目标可以是多单元格范围。

这可能更安全:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    For Each c in Target.Cells
        If Not Intersect(c, Range("H:H")) Is Nothing Then
            '3 ways to accomplish the update 
            Range("A" & c.Row).Value = "Look at me!"
            c.EntireRow.Cells(1).Value = "Look at me!"
            Cells(c.Row, 1).Value = "Look at me!"
        End If
    Next c
End Sub