# Worksheet change event occurs when a time lookup is done.

#### Zimmerman

##### Well-known Member
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
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:

Replies
0
Views
236
Replies
5
Views
171
Replies
1
Views
348
Replies
3
Views
224
Replies
0
Views
561

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.

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.

### Which adblocker are you using?

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

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