Forum Discussion
Conditional formatting? highlight a cell based on current time
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.
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.
I hope you still see these messages, I'm trying to do this as well, it's been 3 days of struggling and nothing is working. I have made my planner in spreadsheets first, and it works there.
Basically, I have 4 columns, E:H, column E has the hours 10:00, 11:00,... and column H has the half hours 10:30, 11:30. And I want to use columns F and G to schedule my day. I want the cell of the current time to be highlighted.
For example, row 6 holds the hours 10:00 and 10:30. If the time is 10:14, I want cell F6 to be highlighted by checking E6 and H6, if the time is 10:34, I want cell G6 to be highlighted by checking H6 and E7 (11:00). As I've said before, i have managed to do this in spreadsheets with the following formula: =($E6 < timevalue(now())) * (timevalue(now()) < $H6) This formula, however, won't work, and many others that I've read on the internet and in communities either, but I feel like it has to be possible since it works in spreadsheets, can you help me?
Assuming that the times begin in row 2, use
=AND(MOD(NOW(),1)>=E2,MOD(NOW(),1)<H2)
for column F, and
=AND(MOD(NOW(),1)>=H2,MOD(NOW(),1)<E3)
for column G.
See the attached sample workbook. Note that I added midnight the next day at the bottom of column E.
HansVogelaar Thank you : )