MFish
Board Regular
- Joined
- May 9, 2019
- Messages
- 76
Hi there, I have this VBA Code in my sheet and a module -
So, in Cell B2, it's a running clock when I set the hotkey to "Ctrl-k". Once "Ctrl-k" is clicked the timer works. No complaints there.
Now, what I'm trying to accomplish is to have a CF on a cell referring to the actual time. I have employees that start at random times, 5 am, 7 am, 7:30 am, etc... and I want to reference their start times to when they should be taking a lunch, through CF.
I need the employees to take their lunch before 5 hours of work-time, depending on their actual start time. I.E., if Billy started work at 5 am, then he must take his lunch by 10 am.
I want the cell (Where I type in his start time - I7) to reference the actual time in B2 to notify the user by "Green", "Yellow", and "Red". The color schemes would reference "Green" for the first 4 hours working, no problem. From hour 4-5, the filled background of that cell would reach "Yellow". Anything past 5 hours would be "Red". Now, once I put a value in I8, as a starting time for their lunch, the CF goes back to nothing stating the person has taken their lunch and shouldn't worry about them.
Is this possible?
Code:
Option Explicit
Dim SchedRecalc As Date
Sub Recalc()
With Worksheets("Sheet1").Range("B2")
.Value = Format(Time, "hh:mm:ss AM/PM")
End With
Call SetTime
End Sub
Sub SetTime()
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "Recalc"
End Sub
Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
End SubSo, in Cell B2, it's a running clock when I set the hotkey to "Ctrl-k". Once "Ctrl-k" is clicked the timer works. No complaints there.
Now, what I'm trying to accomplish is to have a CF on a cell referring to the actual time. I have employees that start at random times, 5 am, 7 am, 7:30 am, etc... and I want to reference their start times to when they should be taking a lunch, through CF.
I need the employees to take their lunch before 5 hours of work-time, depending on their actual start time. I.E., if Billy started work at 5 am, then he must take his lunch by 10 am.
I want the cell (Where I type in his start time - I7) to reference the actual time in B2 to notify the user by "Green", "Yellow", and "Red". The color schemes would reference "Green" for the first 4 hours working, no problem. From hour 4-5, the filled background of that cell would reach "Yellow". Anything past 5 hours would be "Red". Now, once I put a value in I8, as a starting time for their lunch, the CF goes back to nothing stating the person has taken their lunch and shouldn't worry about them.
Is this possible?
