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

Peterson · 05-08-2021, 11:13 PM

I have a Change Event macro that, for a given row in a defined table, adds the word "Active" to a cell in Column V when a user changes text in Columns A, B, or C. If the user clicks out of Column A-C after entering data and they click on a different row elsewhere in the table, then "Active" is entered in that clicked row.

How can I make sure that if the user enters data in a row, "Active" is entered in the same row, regardless of where the user clicks? Here's the code, also attached:

Code:

Private Sub Worksheet_Change(ByVal Target As Range) ' 05/08/2021
 ' Run if the target is in Columns A-C and is in Table1 (use Cols A-C, as
' any of these might be the first cell filled out for a new hearing).
' Add "Active" to the Job Status column:
    
    Dim rngActiveCell As Range
    Dim lngLastRow As Long
    Dim lngTblColNum As Long
    
    If Target.Column < 4 Then
        Set rngActiveCell = ActiveCell
        lngLastRow = ActiveCell.Row
        'If the active cell is in Table1:
        If Not Intersect(ActiveCell, [Table1]) Is Nothing Then
            Range("V" & lngLastRow).Value = "Active"
        End If
    End If
End Sub