Macro on a data validation list
 

I have a Time sheet with a row G7:G:12 that contains Data Validation List.
In this list I have a value 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 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.
I tried recording a macro but of course that was to specific to that
recorded row and would not work with other rows.
What I need is some guiding where to find a similar code that can be alter
to fit my needs. Been looking in google but really don't know what keywords
to search for.

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.

Kevin Brenner

Macro on a data validation list
 

Kevin

Try this Worksheet_Change event code. I put comments in it, but post back
if you have questions.

Private Sub Worksheet_Change(ByVal Target As Range)

'Make sure changed cell is in G7:G12
If Not Intersect(Target, Me.Range("G7:G12")) Is Nothing Then

'With column I on same row
With Me.Range("I" & Target.Row)

'If holiday selected
If Target.Value = "Holiday" Then

'Add +7 to the formula
.Formula = .Formula & "+7"

'If holiday not selected
Else

'If formula already has a +7 then remove it
If Right(.Formula, 2) = "+7" Then
.Formula = Left(.Formula, Len(.Formula) - 2)
End If
End If
End With
End If

End Sub

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Kevin" ! wrote in message
...
I have a Time sheet with a row G7:G:12 that contains Data Validation
List.
In this list I have a value 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 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.
I tried recording a macro but of course that was to specific to that
recorded row and would not work with other rows.
What I need is some guiding where to find a similar code that can be alter
to fit my needs. Been looking in google but really don't know what
keywords
to search for.

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.

Kevin Brenner