+ Reply to Thread
Results 1 to 11 of 11

Search 1st Column for Date older than 5 days based on 2nd column contents

  1. #1
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Search 1st Column for Date older than 5 days based on 2nd column contents

    Hello,

    I am trying to figure out how to check for dates on Workbook_Open when there are dates older than 5 days from the current date in Cell B1 but only if Cell D1 is empty. Then a message box that shows "You have a total of 6 jobs older than 5 days". I have played around with conditional formatting a little bit and it would be nice to even have the rows turn yellow that have this result of older than 5 days, but I am quite new to Excel so I really need the help of an expert.... but I want to be able to learn as I go. Any help is greatly appreciated. Thanks

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Search 1st Column for Date older than 5 days based on 2nd column contents

    Hi

    If you want to do this, using Conditional Formating, then do this.

    If your dates are in Column A,highlight your range and then in Conditional format rules, put this formula

    Please Login or Register  to view this content.
    Choose your color.>>OK.

    Is this works for you?
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Contributor
    Join Date
    05-25-2012
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    194

    Re: Search 1st Column for Date older than 5 days based on 2nd column contents

    For a macro that executes on workbook open, you could use the following under your workbook_open event; Keep in mind I'm assuming you have 1 worksheet, that you have multiple rows of data and that you have formatted your dates correctly on your sheet.
    Please Login or Register  to view this content.
    Hope this helps.

  4. #4
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Re: Search 1st Column for Date older than 5 days based on 2nd column contents

    Hi Medpack

    Thanks for the help... I have been trying to play around with this and I can't seem to get anything but "No jobs older than 5 days" message to show. My start dates are in column C (3) and my finish dates would be in Column S (19). I have put a few dates older than 5 days in column C and left some in column S blank but still no results. Also, if use (xlDown) I get a debug error. SO I was using (xlUp). Here is the code so far from what you suggested:

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Re: Search 1st Column for Date older than 5 days based on 2nd column contents

    Hello fotis1991

    Thanks for your help.... I am looking at my actual file now and my job start date is in column C and my finish date is in column S which could be blank. So I want to check if column C dates are older than 5 days from today IF column S is blank. How will this change your formula ? Thanks

  6. #6
    Forum Contributor
    Join Date
    05-25-2012
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    194

    Re: Search 1st Column for Date older than 5 days based on 2nd column contents

    Try this (I'm on a pc with excel so I was able to test it);

    Please Login or Register  to view this content.

  7. #7
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Search 1st Column for Date older than 5 days based on 2nd column contents

    Hi

    See the example..
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Re: Search 1st Column for Date older than 5 days based on 2nd column contents

    Hi Medpack

    Thanks for helping me out.... this is getting me VERY close to what I need. I went back to the "For Loop". My date start row begins at Column C8 but it seems to color 1-6 as well so I want to ignore those. Also, when I update a finish date that turned Red on the previous workbook_open, how can I get it to turn white again? I need it to show each time the workbook is opened, the user will get the message that "x" number of jobs are older than 5 days and it will turn those cells in Column S red and once they are updated, they will turn white. Thanks again for your time and help, greatly appreciated.

  9. #9
    Forum Contributor
    Join Date
    05-25-2012
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    194

    Re: Search 1st Column for Date older than 5 days based on 2nd column contents

    Ok, changing the beginning of the for loop (e.g. For i = 8) will cause your "search range" to go from C8 to the last value in column C.

    Please Login or Register  to view this content.
    In regards to having the workbook change cell colors back via vba once you've updated a cell, there's a few solutions, but to be honest, unless you're running 2010, these will probably process slowly and really aren't worth it. I'm again on a pc that doesn't have Excel, so I can't test it out, but the basic idea is you set an event that runs on calculate, check if the color of the selected cell is = yellow (e.g. 6, or red e.g. 3 - depending on what color you're using), and then from there just set it back to 0 or 1 (I forget which color is white, again just google "index color excel vba" and you'll find a chart).

    Basically, you change a date (e.g. force the worksheet to calculate), the macro triggers off this event, you check if your target is colored as red or yellow, if it is, turn the entire row back to white.

    Sorry this is such a sloppy explanation, but again I'm back on a PC without Excel, so I know this is all possible, but I just don't know the exact syntax off the top of my head without testing it.

  10. #10
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Re: Search 1st Column for Date older than 5 days based on 2nd column contents

    Hi Medpack,

    Thanks a lot for your help and your time.... very much appreciated. I was able to get this to finally work the way I need it to. I just put an "IF" statement before the code to put all back to white and then to red once it runs the code. I'm learning slowly but surely. Once again, thanks very much!

  11. #11
    Forum Contributor
    Join Date
    05-25-2012
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    194

    Re: Search 1st Column for Date older than 5 days based on 2nd column contents

    Oh I get what you mean, you just wanted it to be reset each time you run the code? Yea, that should do the trick. Glad to be of help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1