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

"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. — SJR
– SJR, Commented Oct 30, 2019 at 21:04
@SJR there is no error message.When I choose a needed value, column B and C are not updated (still same value). — John
– John, Commented Oct 30, 2019 at 21:40

chris neilsen · Accepted Answer · 2019-10-30 21:17:18Z

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

Collectives™ on Stack Overflow

Change columns value based on another cell

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related