CODEWITHSUNDEEP
vbaexcel
puk
puk

Reputation: 16782

Get row number on Cell Change in VBA

I know how to check to see if a column has changed, like so

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

If I want to change another column on the same row, I can do this

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

Now what if I want to achieve the above, but for all columns in the range of row1 to the end of the table? Something like this (note, I know this wouldn't work)

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

Upvotes: 2

Views: 14062

Answers (1)

Tim Williams
Tim Williams

Reputation: 166351

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

Though you should note that Target can be a multi-cell range if more than one cell is updated in the same operation.

This may be safer:

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

Upvotes: 6

Related Questions