Excel Macro Programming Solved/Closed

Question

Hello,
It's been a while since I last wrote my own macro and I can't remember how to do this one up.

I have a pull down list in excel, using data valadation.  However, I need to have a macro that will update a second cell with a corresponding number based on the details that are selected in the first cell.

For example:

Pull Down List [F4]                   Numerical Cell [L4]
Cookies                                    Cookies changes the value of [L4] to 41
Fries                                        Fries changes the value of [L4] to 47
Chocolate                                 Chocolate changes the value of [L4] to 42
Sex                                          Sex changes the value of [L4] to 45
Books                                       Books changes the value of [L4] to 49
Computers                                Computers changes the value of [L4] to 43

Can someone help me with this?

Thanks.

venkat1926 · Answer

in F4 you have validation list cookies, fries etc.

now right click the sheet tab and click view code
in the resulting window copy paste this event code 
save the file 

now go to F4 choose from the list and see what happens in L4

post back comments

the event code is 

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$F$4" Then Exit Sub
Dim rf As Range, rl As Range
Set rf = Range("F4")
Set rl = Range("L4")
If rf = "cookies" Then rl = 41
If rf = "fries" Then rl = 47
If rf = "chocolate" Then rl = 42
If rf = "sex" Then rl = 45
If rf = "nooks" Then rl = 49
If rf = "computers" Then rl = 43
End Sub

Excel Macro Programming

1 response

Similar discussions

Subscribe To Our Newsletter!