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.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

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:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,800
Messages
5,513,501
Members
408,954
Latest member
Tiaame

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top