Highlight current time with CF

Try this...

Let's assume the data is in the range A2:A25

Select the *entire* range A2:A25 starting from cell A2.
Cell A2 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

Goto the Home tab>Styles>Conditional Formatting>
Manage rules>New rule>Use a formula to determine
which cells to format

Enter this formula in the box below:

=HOUR(A2)=HOUR(NOW())

Click the Format button
Select the desired style(s)
OK out

Hi and welcome to the forum

Use a Conditional Format. e.g.

Formula:

Please Login or Register  to view this content.

Go to Conditional Formatting, Manage Rules, select the rule for the time and where it says APPLIES TO change the reference to include the column that you want the highlight to extend to.

Amend the rule to be =HOUR($A2)=HOUR(NOW())

Eg $A$1:$C$25 would extend the formatting to column C

It dawned on me that this may not work as you anticipated.

If you open the file at say 9:58 AM then the row that corresponds to 9:00 will be highlighted.

If you still have the file open at say 10:01 AM the row that corresponds to 9:00 may still be highlighted.

This is because the formula will not automatically update until some event causes a calculation to take place (assuming you have calculation set to automatic).

I wonder if there is some kind of VBA counter that could cause an "event" every couple of minutes to keep the correct time formatted.

There is. It's called OnTime.

You would set a timer and every x interval the OnTime event can trigger a calculation.

Just don't ask me how to program it!

It doesn't look to be a simple task, at least for me

Either that, or instead of using conditional formatting and a calculation event, the formatting could be applied directly with the OnTime event.

Hi There,

I am stuck with a similar problem. I have a schedule over the course of the day from 04:30 - 01:45 (next day). Time is going in 15 min blocks i.e. 10:00; 10:15; 10:30; 11:00....etc.

I would like a single red line to automatically move along the appropriate cell as the day goes along. I have manually created what it is roughly that I want, with cell B5 showing the current time using the =NOW() formula.

There is a formula that does one for days (not minutes) with the formula (assuming cell D4 is the first cell of the days)
=IF(D$4=TODAY(), TRUE, FALSE)
This works perfectly - I can format for there to be just one single red line and it goes far down the spreadsheet - up until where I had highlighted.

But I want this to be every 15 minutes. Not even hourly.

When I tried the formula earlier =HOUR(A2)=HOUR(NOW()) this was close, but it showed 3 red lines as it was covering over the course of the hour, understandably. Also, the red line would only cover the length of the single time cell - I wanted the line to extend downwards (like in the attached image).

Is there a formula I can use that will mark one long line for every 15 minutes?
Conditional Formatting 15 MINS.JPG
Thank you!