Run macro based on cell value

Question

I have a pull down (data validation) in F15. Based on the selected name, i have a vlookup in I15 that will display a corresponding number. When F15 changes (worksheet change), I need to run macro "x" which will be the corresponding number in I15. 

Example: If Amy's number is 6

If Amy's name is selected in F15, then 6 will appear in I15 (as a result of a vlookup). Once the value 6 appears, run macro 6. 

Example: If Bob's number is 1

If Bob's name is selected in F15, then 1 will appear in I15  (as a result of a vlookup). Once the value 1 appears, run macro 1.

Answer 1

The code will need to be called on the change event for cell F15 (not I15) because the events do not detect the change in I15 when the formula updates it. (Calculate event will run but will also run with any other calculation on the worksheet so not suitable to use.)

The value in I15 can still be used to control which macro runs like the following.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$F$15" Then

        Application.Run  "Macro" & Range("I15").Value

    End If

End Sub

If you have other code running on the Change event then it will need to be handled with If or Case statements that determine which cell has been changed.

I am assuming that you know how to install the code in the worksheets module.

Answer 2

Thanx!

When I make my first million... I'm buying you a Krystal, WITH cheese!