Dynamically updating border based on current time

DarrenK

New Member
Joined
Aug 5, 2017
Messages
33
Hello,

This is (I feel) the toughest thing I've attempted so far. Everyone in the forums has been amazingly helpful. This request I think will require VBA because I don't know if it's possible without as it would require a dynamic clock amongst other things. My company doesn't have any schedule adherence tools so I was going to see if it was possible to generate through Excel. And apologies as I can never get the copy/paste to include the headers no matter how many times I try.

B1 will have my dynamic clock. I was not sure if that meant it needed to be a MOD or just a NOW formula. But all cells will reference that for the current time (and it needs to dynamically update without the use of F9). B3:??3 will have 5 minute increments up through 6:00pm. I plan on creating =B3...=??3 cells formatted with General numbers to convert times to decimals. Those are the cells the VBA will have to reference I presume.

1604348751215.png


What I would like to see...is it possible to cause the border between A4 and B4 (and of course continuing downward to the final agent) to shade to the double thick line when the clock hits 0800? And then once it hits 0805, the border between A4 and B4 will go away and the border between B4 and C4 will darken. This will show that it has hit 8:05am. I was going to shade the breaks and lunch periods for each rep within the respective rows so that way anyone using the spreadsheet can see if the rep should be at break at that time. The formula will run all the way through 1800.

I know 1 minute increments would allow for a smoother movement of the border lines. And I'm not opposed to adding the extra columns and shrinking the width. But is this something that can be done?

Thank you in advance,
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Watch MrExcel Video

Forum statistics

Threads
1,119,223
Messages
5,576,827
Members
412,748
Latest member
MikeyP14
Top