How to quickly jump to the adjacent cell based on the selection in an Excel dropdown list?

The typical user of Microsoft Excel can benefit from the use of a dropdown list in order to simplify the data entry process. When entering data into a selected cell, using a drop-down to restrict entry options expedites the process and reduces the likelihood of making errors. Using the Data Validation feature of Excel, which will be covered in this post, you may quickly and easily generate a drop-down list of options. You can construct a drop down list in Excel by naming a list of things that is based on an Excel table that has a specific name. After that, make that list the source for the Data Validation drop down list you're going to create.

On the other hand, there are instances when you might wish to choose a cell in Excel in such a way that the selection is determined by the options presented in yet another drop-down list. You might have ever thought about how to move to the next cell over in Excel based on the selection you make in a drop-down list. In this tutorial you are going to learn about the selection of a cell based on another selection from the drop-down list using VBA.

Jump To Adjacent Cell Based On Value In Drop Down List By VBA

Step 1

In our example we have the days and dates in an excel sheet with a dropdown list which contains the days from Monday to Sunday. See the below given image.

Step 2

To add the VBA code in it, open the Microsoft Visual Basic for Applications window, by pressing the Alt key and the F11 (Alt+F11) key simultaneously.

Step 3

Then click on Insert>Module and the popup Module window will be opened.

Step 4

In Module window, type the following VBA code.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address <> [C2].Address Then
      Exit Sub
   Else
      JumpToCell
   End If
End Sub

Sub JumpToCell()
   Dim xRg, yRg As Range
   Dim strAddress As String
   strAddress = ""
   Set yRg = Range("A2:A8")
   For Each xRg In yRg
   'MsgBox Cell.Value
      If xRg.Value = ActiveCell.Value Then
         strAddress = xRg.Address
      End If
   Next
   If strAddress = "" Then
      MsgBox "Invalid Selection" & ActiveSheet.Name, _vbInformation, "VBOutput"
      Exit Sub
   Else
      Range(strAddress).Offset(0, 1).Select
   End If
End Sub

See the below given image.

In above code, we have added the cell range A2:A8 and in “Target.Address <> [C2].Address “ C2 is the cell in which the dropdown list is created. Change these cell according to your need.

Step 5

Click Run or F5 to run the VBA code. See the below given image.

When the above VBA code is run, the cursor moves to the next cell based on what is in the drop-down list.

See the below given image.