Forum Discussion
Conditional formatting? highlight a cell based on current time
Working on an hourly To-Do list.
I have a column (H) with the listed hour (on the Hr) starting with 5am all the way until 11pm.
For Example:
6:00 AM
7:00 AM
8:00 AM
9:00 AM
The ask: I would like the 7:00 AM cell be highlighted when the current time is between 7-7:59 AM. Then the next cell once it hits between 8 and 8:59 and so on.
Thank you and let me know what you think?
Let's say the times are in H2:H20.
Select this range. H2 should be the active cell in the selection.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to detemine which cells to format'.
Enter the formula
=HOUR(H2)=HOUR(NOW())
(Remember, H2 is the active cell in the selection).
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.
Let's say the times are in H2:H20.
Select this range. H2 should be the active cell in the selection.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to detemine which cells to format'.
Enter the formula
=HOUR(H2)=HOUR(NOW())
(Remember, H2 is the active cell in the selection).
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.
- What if I wanted to highlight the whole row instead of just the cell with the time in it? Highlight all of row 2 in this case?
Select rows 2 to 20 (or however far down you want).
Follow the steps from my previous reply, but use the formula
=HOUR($H2)=HOUR(NOW())
The $ before the column letter H ensures that the rule "looks at" column H in all cells of a row.
- Perfect. Thank you!!
- Just want to enhance the answer, if you have list of half-an hour column like:
6:00 AM
6:30 AM
7:00 AM
7:30 AM
you can use this formula: =AND(HOUR(H2)=HOUR(NOW()),MINUTE(H2)=(IF(MINUTE(NOW())<30,0,30))).
It's the same formula provided by Hans with addition checking minute of the listed hour column, whether 0 or 30.Najmi6240, thank you this was very helpful, just want to share with others that if you are working with 15-minute increments you can simply change the 30 in this formula to 15.
Peaceful82 Hello, I've tried this on mine and it only highlights at o'clock and quarter past, is there a way to also get it to highlight at half past and quarter to?
thanks
