I have the following on my spreadsheet:

A1 = Date
A5 = Begin Time
A6 = End Time
A7 = A1+A5 'Giving me the Beginning Date & Time
A8 = A1+A6 'Giving me the End Date & Time

What I want to do is whenever the user enters a time in Cell A9, either a
data validation or vba check that time entered is between the value in Cell
A7 and A8. The assumption is that the value in Cell A9 is A9+A1 but I only
want the user to only enter the time. It is probably easier just to have a
format of Date/Time throughout but I am constraint by the design format. I
tried with the validation with a custom formula: = AND(A9+A1A7, A9+A1<A8).
This just gave me a error for any number I entered. And I tried a event
procedure (Note the target.column and target row do not match the cells
above):

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 15 And Target.Row = 10 Then
If Target.Value + Target.Offset(-9, 2).Value
Target.Offset(-1, 0).Value And Target.Value + Target.Offset(-9, 2).Value
Target.Offset(-1, 1) Then
Target.Value = Target.Value
Else
MsgBox ("Time entered does not fall within work shift.")
Application.Enableevents = False
Target.Value = " "
Exit Sub
Application.Enableevents = True
End If
End If
End Sub

But this does not work either. Any help is appreciated.

--
Thanks!