Time of day adjusting color in cell

[Franklin1179]

Hello, I am working on a global schedule for technicians using Excel. I would like a way to have the color in the cell in front of the technician name to change based on the time of day. Also I would like for the cell in front the cell to show number of hours left until their work shift ends. I would appreciate some assistance, I am out of ideas.

 

[JLGWhiz]

Hello, I am working on a global schedule for technicians using Excel. I would like a way to have the color in the cell in front of the technician name to change based on the time of day. Also I would like for the cell in front the cell to show number of hours left until their work shift ends. I would appreciate some assistance, I am out of ideas.

Copy this to the worksheet code module. Right click on the name tab of the sheet and click 'View Code' in the pop-up menu to access the code module. After the code is installed, each time the sheet is activated the code will run and the times will update. This code is set at times between 8 Am and 10 Am color is green, between 10 Am and 1 PM color is yellow, After 1 Pm color is red.

Private Sub Worksheet_Activate()
Range("A1") = Time
Range("A2") = Format(#10:00:00 PM# - Time, "h.n")
If Time < #10:00:00 AM# Then
    Range("B2").Interior.ColorIndex = 10
ElseIf Time < #1:00:00 PM# Then
    Range("B2").Interior.ColorIndex = 6
ElseIf Time < #3:00:00 PM# Then
    Range("B2").Interior.ColorIndex = 3
Else
    Range("B2").Interior.ColorIndex = 0
End If
End Sub

 

[Franklin1179]

Thank you very much with your assistance. This is what I was looking for. I feel bad asking this question, how to I add more code to this module for the rest of the technicians.