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
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