<div>
Please note that the very last question was added as an edit (i.e. early birds may have missed it).
</div>

Book1.xlsx

<div>
As usual Teylyn's answers not only grat but with minute details !!! 
 
To complement Teylyn's answer and to make the workbook active, as presume you need the showing of the time not only when workbook is opened but all along the workbook beeing active and even when opened and idle. I suggest the following: 
 
1) You change your workbbok from simple no macro .xlsx to a macro .xlsm. 
2) you create a Module in which you add the below code 
 
&nbsp;
<pre><code class="code-10 nolinks" id="code-8224617">Global RunWhen

Sub StartTimer()
 ' run it every 1min
 RunWhen = TimeSerial(Hour(Now), Minute(Now) + 1, 0)
 Application.OnTime EarliestTime:=RunWhen, Procedure:=&quot;Calculate&quot;, Schedule:=True
End Sub

Sub Calculate()
ActiveSheet.Calculate
StartTimer
End Sub
</code></pre>
 
3) you add in your wokbook open the following code 
--------------- 
Private Sub Workbook_Open() 
StartTimer 
End Sub 
---------------- 
 
4) you add also in your sheet Worksheet_SelectionChange event the below code 
---------------- 
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
ActiveSheet.Calculate 
End Sub 
----------------- 
 
5) Save exit the workbook and try it. 
 
What this will do is refresh the workbook every minute and run Calculate as without it when the time changes pass 30min it will not change color unless you manually trigger calculate in the worksheet. if your worksheet is opened for say 2 hours and you do not do any calculation the time will show the old value whereas with this code even if the workshhet is inactive for as long as you want the time will indicate correctly. even is you simple move the cell selection it will update the time. 
 
Hope this will be use\ful for you as feel you need the coloring (which is a very smart idea to be used in a clendar type where you would hv your appointment and somthing indicating the actual time !! that's how I see it. 
 
PS for your easy refrence I have incorporated the code in Teylyn's attached file for you to see it better. For sure all the credit goes to Teylyn as my suggestion was not in the scoope of the question but felt it might be useful to you. 
 
Enjoy 
gowflow 
<a href="https://filedb.experts-exchange.com/incoming/2011/10_w41/509401/TimeChange-Formating.xlsm" target="_blank" class="file-inline" title="Auto TimeChange format (18 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>TimeChange-Formating.xlsm</a>
</div>

TimeChange-Formating.xlsm

<div>
Just one small question, Steve. Do you expect B35 and C35 to ever be coloured yellow - or don't you care at that time of day? 
 
regards, barry
</div>

<div>
...if you do want row 35 to be highlighted when the time is between midnight and 07:00 you can do this: 
 
Make sure B35 = 0 not 1 as per teylyns setup. Select B35:C35 and apply this formula in conditional formatting 
 
=$B1=MAX(IF($B$1:$B$35&lt;=MOD($A$1,1),$B$1:$B$35)) 
 
format as required 
 
See attached, A1 randomly selects a date/time in the next 24 hours - press F9 to re-generate so you can check how the formatting works - For &quot;real&quot; use A1 should revert to =NOW() 
 
regards, barry 
<a href="https://filedb.experts-exchange.com/incoming/2011/10_w41/509433/27380541.xlsx" target="_blank" class="file-inline" title="time and conditional formatting (11 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>27380541.xlsx</a>
</div>

27380541.xlsx

<div>
Note: I believe there is a &quot;bug&quot; in Excel 2007 which affects conditional formatting based on formulas that would need to be &quot;array-entered&quot; if placed in a worksheet cell. When I re-open the workbook after closing I have to go into the conditional formatting before it works correctly (otherwise it seems to erroneously format 11:30 PM when it shouldn't). It shouldn't affect you if opened in Excel 2010 (although I can't check that right now). 
 
If it's an issue then you can change to this formula 
 
=$B1=MAX(INDEX(($B$1:$B$35&lt;=MOD($A$1,1))*$B$1:$B$35,0)) 
 
regards, barry
</div>

<div>
&gt;&gt;barryhoudini: 
...Do you expect B35 and C35 to ever be coloured yellow - or don't you care at that time of day? 
 
The latter, Barry -- but your comments are always welcome and appreciated!
</div>

<div>
<div class="content wysiwyg-content">
Hello, 
 
In an Excel (2010) spreadsheet, suppose that: 
&nbsp; &nbsp; &nbsp;• A1 displays the current date &amp;&nbsp;time (assume that it automatically updates every minute), and 
&nbsp; &nbsp; &nbsp;• B1:B35 displays times beginning with 7:00 AM in B1 and moving down, every 30 minutes thereafter, 
as shown in the following screenshot (note that rows 8-30 are hidden -- red arrow): 
&nbsp;<a href="https://filedb.experts-exchange.com/incoming/2011/10_w41/509369/2011-1004a.PNG" target="_blank" title="a (9 KB)"><img alt="a" class="file-block lazyload" style="max-width: 231px;" data-src="https://filedb.experts-exchange.com/incoming/2011/10_w41/509369/2011-1004a.PNG" /></a>If 
&nbsp; &nbsp; &nbsp;• turn on = cell colored yellow 
&nbsp; &nbsp; &nbsp;• turn off = no cell color (i.e. white or whatever the spreadsheet background color is) 
what Conditional Formatting (CF) formula would cause a given cell in column B to turn on as soon as the time in A1 reached the time shown in the cell and turn off 30 minutes later when the next cell in the column turns on? 
 
Also, 
&nbsp; &nbsp; &nbsp;• What formatting should be in A1 and column B so that the CF will work correctly day after day? 
&nbsp; &nbsp; &nbsp;• While the CF is being applied, should B1:B35 be selected or only B1 after which it is copy/pasted into B2:B35? 
&nbsp; &nbsp; &nbsp;• What formula would be entered in C1:C35 so that the (empty) column C cell would turn on &amp;&nbsp;off with its column B partner? 
 
Thanks 

</div>
</div>

2011-1004a.PNG

Hello,

In an Excel (2010) spreadsheet, suppose that:
     • A1 displays the current date & time (assume that it automatically updates every minute), and
     • B1:B35 displays times beginning with 7:00 AM in B1 and moving down, every 30 minutes thereafter, 
as shown in the following screenshot (note that rows 8-30 are hidden -- red arrow):
 
If
     • turn on = cell colored yellow
     • turn off = no cell color (i.e. white or whatever the spreadsheet background color is)
what Conditional Formatting (CF) formula would cause a given cell in column B to turn on as soon as the time in A1 reached the time shown in the cell and turn off 30 minutes later when the next cell in the column turns on?

Also, 
     • What formatting should be in A1 and column B so that the CF will work correctly day after day?
     • While the CF is being applied, should B1:B35 be selected or only B1 after which it is copy/pasted into B2:B35?
     • What formula would be entered in C1:C35 so that the (empty) column C cell would turn on & off with its column B partner?

Thanks


Time and date related Conditional Formatting question in Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.