how to Compare Real ticking time with time in excel sheet

sanchit16

New member
Joined
Feb 1, 2012
Messages
11
Reaction score
0
Points
0
Location
New Delhi,India
I want to create a macro which can compare real time(real of pc or real time ticking in the excel file) with the time mention in the column of excel sheet ,and if it exceeds the time then a alert message pops up .

I am waiting for your reply ASAP.

For a sample PFA that how I want it.
cleardot.gif



View attachment TIME SAMPLE.xlsm

time sample.jpg
 

lvalnegri

New member
Joined
Jan 31, 2012
Messages
11
Reaction score
0
Points
0
Location
London
I guess the lines I added can get the job right. the change in color is a condition just to avoid the message to be repeated indefinitely

If ThisWorkbook.Worksheets(1).Range("B1").Value = "X" Then Exit Sub
ThisWorkbook.Worksheets(1).Range("A1").Value = Format(Now, "hh:mm:ss AM/PM")

i = 2
Do
If (Cells(i, 4) - Cells(1, 1)) <= 0 And Cells(i, 4).Interior.Color = 15773696 Then
MsgBox ("Time #" & (i - 1) & " has come")
Cells(i, 4).Interior.Color = 255
End If
i = i + 1
Loop Until Cells(i, 4) = ""

Application.OnTime Now + TimeSerial(0, 0, 1), "clock"
 

sanchit16

New member
Joined
Feb 1, 2012
Messages
11
Reaction score
0
Points
0
Location
New Delhi,India
Thanks lvalnegri for your effort

Thanks lvalnegri for your effort

But I am facing one problem,when i am replacing the time in column then its not showing the popup automatically for that time.

can you please attach the sample sheet,so that i can see how exactly it works.


I guess the lines I added can get the job right. the change in color is a condition just to avoid the message to be repeated indefinitely

If ThisWorkbook.Worksheets(1).Range("B1").Value = "X" Then Exit Sub
ThisWorkbook.Worksheets(1).Range("A1").Value = Format(Now, "hh:mm:ss AM/PM")

i = 2
Do
If (Cells(i, 4) - Cells(1, 1)) <= 0 And Cells(i, 4).Interior.Color = 15773696 Then
MsgBox ("Time #" & (i - 1) & " has come")
Cells(i, 4).Interior.Color = 255
End If
i = i + 1
Loop Until Cells(i, 4) = ""

Application.OnTime Now + TimeSerial(0, 0, 1), "clock"
 

lvalnegri

New member
Joined
Jan 31, 2012
Messages
11
Reaction score
0
Points
0
Location
London
I guess you should also change the color of the cell from red to blue when replacing the time: Cells(???, 4).Interior.Color = 15773696
 

sanchit16

New member
Joined
Feb 1, 2012
Messages
11
Reaction score
0
Points
0
Location
New Delhi,India
reply

yes I have tried as per the code and it running.

But still I have to press the macro button to run your macro, and that thing is to be done automatically.

Getting my point buddy.

Can you please attach a sample file so that I can see. And please add maximum column of 50 for time comparision.

I guess you should also change the color of the cell from red to blue when replacing the time: Cells(???, 4).Interior.Color = 15773696
 

lvalnegri

New member
Joined
Jan 31, 2012
Messages
11
Reaction score
0
Points
0
Location
London
I think the lines I wrote solved your problem; as those are the only differences with the original file, an attachment is useless. You are now facing a different problem.
 

sanchit16

New member
Joined
Feb 1, 2012
Messages
11
Reaction score
0
Points
0
Location
New Delhi,India
reply

Your macro is just perfect buddy, I just need to automate it.

so that when real time crosses it generate the popup,I mean I want some kind of auto trigger which run macro every sec so that it can generate the pop up.

Hope you can understand what I need. :nod:
 
Top