Worksheet change event occurs when a time lookup is done.

Zimmerman

Well-known Member
Joined
Jan 22, 2009
Messages
663
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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
Code:
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
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,611
Messages
5,832,694
Members
430,154
Latest member
Froggy16

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top