Microsoft Office Forums - View Single Post - Change Event to change cell in same row doesn't work if user clicks other row

jeffreybrown · 05-09-2021, 05:59 AM

Give this a try. Instead of Worksheet Change, you need to use Worksheet SelectionChange. Also, added a line to remove all previous values and a line to find LastRow of table.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rngActiveCell   As Range
    Dim lngLastRow      As Long
    Dim lngTblColNum    As Long
    Dim LastRowTable    As Long
    If Target.Column < 4 Then
        Set rngActiveCell = ActiveCell
        lngLastRow = ActiveCell.Row
        If Not Intersect(ActiveCell, [Table1]) Is Nothing Then
            LastRowTable = ActiveSheet.ListObjects("Table1").DataBodyRange.Rows.Count + 2
            Range("V3:V" & LastRowTable).Value = ""
            Range("V" & lngLastRow).Value = "Active"
        End If
    End If
End Sub