Results 1 to 7 of 7

Thread: how to Compare Real ticking time with time in excel sheet

  1. #1
    Seeker sanchit16's Avatar
    Join Date
    Feb 2012
    Location
    New Delhi,India
    Posts
    11
    Articles
    0

    Exclamation how to Compare Real ticking time with time in excel sheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.


    TIME SAMPLE.xlsm

    Click image for larger version. 

Name:	time sample.jpg 
Views:	16 
Size:	51.5 KB 
ID:	456

  2. #2
    Seeker lvalnegri's Avatar
    Join Date
    Jan 2012
    Location
    London
    Posts
    11
    Articles
    0
    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"

  3. #3
    Seeker sanchit16's Avatar
    Join Date
    Feb 2012
    Location
    New Delhi,India
    Posts
    11
    Articles
    0

    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.


    Quote Originally Posted by lvalnegri View Post
    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"

  4. #4
    Seeker lvalnegri's Avatar
    Join Date
    Jan 2012
    Location
    London
    Posts
    11
    Articles
    0
    I guess you should also change the color of the cell from red to blue when replacing the time: Cells(???, 4).Interior.Color = 15773696

  5. #5
    Seeker sanchit16's Avatar
    Join Date
    Feb 2012
    Location
    New Delhi,India
    Posts
    11
    Articles
    0

    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.

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

  6. #6
    Seeker lvalnegri's Avatar
    Join Date
    Jan 2012
    Location
    London
    Posts
    11
    Articles
    0
    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.

  7. #7
    Seeker sanchit16's Avatar
    Join Date
    Feb 2012
    Location
    New Delhi,India
    Posts
    11
    Articles
    0

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •