Conditional formatting? highlight a cell based on current time

[andermanrn]

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?

[#2 · Accepted solution]

@andermanrn 

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.

[#3]

Perfect. Thank you!!

[#4]

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?

[#5]

@andermanrn 

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.

[#6]

@Hans Vogelaar Thank you : ) 

[#7]

@Hans Vogelaar 

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?

[#8]

@myrte 

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.

[#9]

Ik neem aan dat je Nederlands spreekt? Excel moet ik helaas gebruiken in het Nederlands, de formule vertaald is dan =EN(REST(NU(),1)>=H2,REST(NU(),1)<E3) , correct? Ik heb deze namelijk geprobeerd en het werkte niet. Alvast bedankt voor de hulp hoor!

[#10]

@myrte 

Heb je de werkmap geprobeerd die ik bijgevoegd had? Zo ja, deed die wat je wilde?

[#11]

Die werkt inderdaad, ik heb mijn Excel in het Engels gezet en nu doet de formule het wel, dankjewel!

[#12]

@myrte 

Als je Excel daarna terugzet naar het Nederlands, zou het nog steeds moeten werken - Excel vertaalt de formule automatisch.

[#13]

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.

[#14]

@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.