Change columns value based on another cell

Question

I want to update 2 columns values based on another columns value (if value change). Suppose I have column A with a list (AA1, AA2, AA3), column B with a list (BB1, BB2), column C with a list (CC1, CC2). If a choose a value "AA1" from column A then Column B value should change to BB2 et column C to CC1. But nothing should happen if the value chosen in column A is different from "AA1". The same process occurs also for value "BB1" in column B. I added a vba but it not working. Also is there another way to do it without running a vba code ? Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim changedCells As Range
 Set changedCells = Range("A:C")
 If Not Application.Intersect(changedCells, Range(Target.Address)) Is Nothing Then
 If Target.Count > 1 Then Exit Sub
 If Target.Column = 1 And LCase(Target.Value) = "aa1"Then
 Cells(Target.Row, 2) = "BB2"
 Cells(Target.Row, 3) = "CC1"
 ElseIf Target.Column = 2 And LCase(Target.Value) = "bb1" Then
 Cells(Target.Row, 1) = "AA3"
 Cells(Target.Row, 3) = "CC2"
 ElseIf Target.Column = 3 And LCase(Target.Value) = "cc2" Then
 Cells(Target.Row, 1) = "AA2"
 Cells(Target.Row, 2) = "BB2"
 End If
 End If
End Sub

• SJR: "not working" is not helpful. Please elaborate - error message, not doing what you want? Also your logic is not clear. And you need to allow for Elses, eg if A is not AA1.
• John: @SJR there is no error message.When I choose a needed value, column B and C are not updated (still same value).
• John: It is working now (typing error). Thanks

Answer 1

Your code is broadly OK, except it will cause an Event Cascade (changing a cell triggers the Worksheet_Change event, which changes a cell, which triggers Worksheet_Change, which ...)

You need to add Application.EnableEvents = False to prevent this (add ... = True at the end)

Here's your code refactored to address this, and a few other minor issues

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim changedCells As Range
 On Error GoTo EH '~~ ensure EnableEvents is turned back on if an error occurs
 Set changedCells = Me.Range("A:C") '~~ explicitly refer to the correct sheet
 If Target.Count > 1 Then Exit Sub '~~ do this first, to speed things up
 If Not Application.Intersect(changedCells, Target) Is Nothing Then '~~ Target is already a range
 Application.EnableEvents = False '~~ prevent an event cascade
 '~~ original If Then Else works fine. But can be simplified
 Select Case LCase(Target.Value)
 Case "aa1"
 If Target.Column = 1 Then
 Me.Cells(Target.Row, 2) = "BB2"
 Me.Cells(Target.Row, 3) = "CC1"
 End If
 Case "bb1"
 If Target.Column = 2 Then
 Me.Cells(Target.Row, 1) = "AA3"
 Me.Cells(Target.Row, 3) = "CC2"
 End If
 Case "cc2"
 If Target.Column = 3 Then
 Me.Cells(Target.Row, 1) = "AA2"
 Me.Cells(Target.Row, 2) = "BB2"
 End If
 End Select
 End If
'~~ Fall through to EnableEvents
EH:
 Application.EnableEvents = True '~~ ensure EnableEvents is turned back on
End Sub

• John: Thanks I do the change