excelvba

Whenever cell in column B is updated, update same row cell in column N with same value


Whenever the user update a cell in column B, I will need the column N cell in same row to update to the same value. Basically col N duplicates whatever value entered in col B.

I used this code, but nothing happened:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column = 2 Then
        ThisRow = Target.Row
        Range("N" & ThisRow).Value = Range("B" & ThisRow).Value

    End If
End Sub

============================= Resolved ========================

Thanks everyone, I went to use and modified Tim's answer as I needed to apply to multiple columns.

I initially tried using 'switch case' since I need to work with multiple pairs of cols, but could not get the 'case' to work for range type.

Feedbacks are welcomed.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim client_B As Range, client_N As Range
Dim PO_D As Range, PO_O As Range


Set client_B = Application.Intersect(Target, Me.Columns("B"))       'any edits in ColB?
Set PO_D = Application.Intersect(Target, Me.Columns("D"))           'any edits in ColD?


If client_B Is Nothing = False Then                                 'Something editted in ColB

    'process each cell in Col B
    For Each client_N In client_B.Cells
        client_N.EntireRow.Columns("N").Value = client_N.Value
    Next client_N

ElseIf PO_D Is Nothing = False Then                                 'Something editted in ColD

        'process each cell in Col D
        For Each PO_O In PO_D.Cells
            PO_O.EntireRow.Columns("O").Value = PO_O.Value
        Next PO_O

End If

End Sub

Solution

  • For example:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        Dim rng As Range, c As Range
        
        Set rng = Application.Intersect(Target, Me.Columns("B")) 'any edits in ColB?
        If rng Is Nothing Then Exit Sub                           'nothing to handle
        
        'process each cell in Col B
        For Each c In rng.Cells
            c.EntireRow.Columns("N").Value = c.Value
        Next c
    End Sub