» Replace value from validation list with a number using VBA in Microsoft Excel

Question:

The selection from a validity list in column D shall be replaced by a number. Works only with XL9 and higher.


Answer:

Place the code below into the This Workbook module.

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Column <> 4 Then Exit Sub

   Application.EnableEvents = False

   Target.Value = WorksheetFunction.VLookup(Target.Value, Range("A1:B12"), 2, 0)

   Application.EnableEvents = True

End Sub