Worksheet change event occurs when a time lookup is done.

[Zimmerman]

I have a sheet that in column B are times of 6:00 a.m. on down to 2:00 p.m. and in column c are times of 6:30 a.m. on down to 2:30 p.m. in cells F9 thru k9 are times in which a user will enter. How can i have these times the user enters look up in column B and C and if the time falls between the two times a worksheet change event will occur. so say cell B14 is 8:00 a.m. and cell C14 is 8:30 a.m. and cell G9 is 8:15 a.m., in cell G14 a worksheet change will occur.

 

[MickG]

Hi, Try this:-
NB:- If the the time entered in col "D" is between times in Col "B & C", Then col "G" Cell turns Red.
Nb:- Make sure all columns Have same time format

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]If[/COLOR] Target.Count = 1 And Target.Column = 4 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Target.Value > Target.Offset(, -2).Value And Target.Value < Target.Offset(, -1).Value [COLOR="Navy"]Then[/COLOR]
        Target.Offset(, 3).Interior.ColorIndex = 3
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick