Apply list selection to multiple columns

[patiofern]

Hello,

I found this piece of code online and am trying to adapt it to work in a range of columns and multiple cells in that column. The code currently adds a dropdown list to the column (column 2 in as written here) then converts the value selected from the dropdown list to a corresponding numerical value in the "dropdown" range. I would like to have the ability to apply this list selection/conversion to numerical value to a range of columns rather than just a single column. Please see code below . . . Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
selectedNa = Target.Value
If Target.Column = 2 Then
selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False)
If Not IsError(selectedNum) Then
Target.Value = selectedNum
End If
End If
End Sub

 

[DanteAmor]

I would like to have the ability to apply this list selection/conversion to numerical value to a range of columns rather than just a single column.

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  'Updateby Extendoffice
  Dim rng As Range, c As Range
  Dim selectedNum
  
  Set rng = Intersect(Target, Range("B2:B20, D2:D20, F2:F20"))
  If Not rng Is Nothing Then
    For Each c In rng
      selectedNum = Application.VLookup(c.Value, ActiveSheet.Range("dropdown"), 2, False)
      If Not IsError(selectedNum) Then
        Application.EnableEvents = False
        c.Value = selectedNum
        Application.EnableEvents = True
      End If
    Next
  End If
End Sub