Execute a Macro when specific value is selected in a Range

[Kevin]

I really need some help with this problem
I have a Time sheet with a row G7:G:12 that contains Data Validation List.
In this list I have one of many values named Holiday, what I like to do is
when you
select this value "holiday" form the drop down list in cell G7 it would
automatically trigger a macro to add 9:00 am to cell D7, 1.0 to cell E7 and
5:00 pm to cell F7.
It should work the same for the rest of the other rows from G8:G12. I would
actually would like just to paste a value of 7.00 to cell I7 where "Hours
Worked" are but the formula would be deleted, and the sheet will not be
useful.

A nother idea that I had was to validate the cells G7:G:12 so a message
would appear when the word "holiday" was chosen, but those cells already
have data validation to create the list, so no luck there.

I been looking in here in the excel newsreader and in goggle for similar
code so I could tweak it, but I do not understand enough VB to find similar
code out there that I could alter.
Thanks again for all your help, and hope I haven't been to much trouble this
last month asking for help in the forum, you guys are great help.
Any ideas are welcome
Kevin Brenner

 

[Bernie Deitrick]

Kevin,

You can use the worksheet's change event. Copy the code below, right
click on the sheet tab and select "View Code" and paste the code into
the window that appears. Then anytime you enter "Holiday" (without the
quotes) into cells G7:G12, your values will be auto-entered in columns
D, E, and F of the same row.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Not Intersect(Target, Range("G7:G12")) Is Nothing Then
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("G7:G12"))
If myCell.Value = "Holiday" Then
Cells(myCell.Row, 4).Value = "09:00:00"
Cells(myCell.Row, 5).Value = 1#
Cells(myCell.Row, 6).Value = "17:00:00"
End If
Next myCell
Application.EnableEvents = True
End If
End Sub

 

[Kevin]

Thank You SO much!
I been trying to find out how to do this VB code for a wile. I did have the
code with the change event and some other variables, the problem is that I'm
so new at VB I get confused and the lack of knowledge of syntax makes my
worksheet a challenge. One way around was to add a bunch of IF statements to
some formulas to add those values when he word holiday was entered.
well, thanks again.

Kevin Brenner

 

[Terry Frost]

Kevin,

Here is a starting point for you, just place this code in the worksheet you
are working with. Adding more if statements to cover the cells you are
wanting to monitor for changes.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells = Range("G7") Then
If Range("G7") = "Holiday" Then
Range("D7") = #9:00:00 AM#
Range("E7") = Val("1.0")
Range("F7") = #5:00:00 PM#
Range("I7").Formula = "=(F7-D7)-TIME(E7,0,0)"
MsgBox "G7 = Holiday"
End If
End If
End Sub

Also, adding or subtracting time in excel can be confusing. Look at the
line Range("I7").Formula = "Your formula here, how it would look in the
formula bar"
in between the quotation marks

Terry F