• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Range lookup

P

pucha

Member
Hi,


I want to match the current time now() function in a cell with the range of time in Column A and highlighting by conditional formatting.


05:30 AM

05:31 AM

05:32 AM

05:33 AM

05:34 AM

05:35 AM

05:36 AM

05:37 AM

05:38 AM

05:39 AM

05:40 AM

05:41 AM

05:42 AM

--------

--------

--------


thanks

regards


pucha
 
Good day pucha


As the now() function is changing by the millisecond will you ever get a match??
 
Good evening Kaushik03


I thought this may work =HOUR(NOW())&MINUTE(NOW()).......but even though it displays the hour and minute as it is now I can not get CF to change the format colour, still seem to be time changing to quickly.
 
Hi


Any solution to this problem. VBA code etc.


Or simply highlighting the time column as per system time.


Thanks


pucha
 
Hi Pucha,

*
Code: 
Now() provides us Date & Time (22-07-12 11:41), and you want to check only Time part (00-01-00 11:41).

* Assume provided all are in Time Format.. (not Text)


Use Conditional Formatting Cell Value between..

[code]=NOW()-INT(NOW()) and [code]=NOW()+TIME(0,1,0)-INT(NOW()+TIME(0,1,0))


To highlight in each minute you have to write macro using Application.OnTime[/code] to Calculate[/code] on each minute

OR

you can press F9 to refresh the Highlight of cell whenever you need to check current time.


PS: I know its highlighting (current time + 1) section, but I still not able to manage it. :(

Hope someone will comeback to us, to resolve it.


Please find the attached Sheet for your reference.

https://www.dropbox.com/s/tx3nuk32vtag6vo/Time.xlsx


Regards,

Deb
 
Hi Deb,


Thanks for solving and the attached sheet. But its showing one minute ahead the system time after refresh.


Will you please help me to write the macro for refreshing each minute.


Thanks


Pucha
 
Hi Pucha,


Please find the attached updated Sheet.


https://www.dropbox.com/s/ptegfhpnjie31fn/Time.xlsm


Use Conditional Formatting Cell Value between..

=NOW()-TIME(0,1,0)-INT(NOW()-TIME(0,1,0)) and =NOW()-INT(NOW())


To refresh in Each Minute use the below VBA

[pre]
Code: 
Private Sub Workbook_Open()
RunOnTime
End Sub

Sub RunOnTime()
Calculate
dTime = Now + TimeSerial(0, 0, 30)
Application.OnTime dTime, "RunOnTime"
End Sub
[/pre]

It refreshing in each 30 Seconds..


Regards,

Deb
 
Thanks Deb,


Its working very nice. Now its running with system time. And in the VBA code I put 1 for each second refreshment.


Thanks


"Bhalo Theko"


Regards

Pucha
 
contento recibido esos comentarios de ustedes ..

gracias..


Can you please help me to get only Decimal Part from a Number...

I am not happy with the below code to fetch TIME.. from NOW()..

Code: 
=NOW()-INT(NOW())
 
You must log in or register to reply here.
Back
Top