+ Reply to Thread
Results 1 to 17 of 17

Macro to highlight date cells based on criteria linked to "today"'s date

  1. 11-05-2013, 10:18 AM #1
    Gti182
    Gti182 is offline
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    459

    Macro to highlight date cells based on criteria linked to "today"'s date

    cross link here but not luck on responses yet:
    http://www.mrexcel.com/forum/excel-q...ay-s-date.html

    I posted about this yesterday and decided to go the conditional formatting route but it slowed down the file quite noticeably which was already quite slow to start off with hence deciding on the macro route so i've put up a new post.

    My aim is to highlight date cells appearing in columns N, P, R, T, V, X, Z, AB either in RED or AMBER/ORANGE if they fall into the below date criteria.

    Highlight ORANGE - if the date is within 2 weeks of today's date (including today)
    Highlight RED - if the date is within 2 weeks before today's date (excluding today)

    i.e.
    14 days <-----HIGHLIGHT RED------>TODAY<-----HIGHLIGHT AMBER------> 14 days

    - Headings are in the first row and the dates start from row 2 and shouldn't populate more than a 1000 rows
    - Some cells might be blank
    - Macro would clear any highlighting it had previously made in these columns and insert new highlighting when re-activated on say a different day.
    Reply With QuoteRegister To Reply
  2. 11-05-2013, 11:24 AM #2
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Macro to highlight date cells based on criteria linked to "today"'s date

    Maybe:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 11-05-2013, 11:35 AM #3
    Gti182
    Gti182 is offline
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    459

    Re: Macro to highlight date cells based on criteria linked to "today"'s date

    Hi John,

    thanks very much for this, that is an impressive bit of code and seems to do the trick! It does however take a while too run +- 3 min. Also i see it removes the cell highlighting and text colour of the first row which contain all my headings. Any suggestions?

    amended code below in attempt to speed up the macro

    Please Login or Register  to view this content.
    Last edited by Gti182; 11-05-2013 at 11:59 AM.
    Reply With QuoteRegister To Reply
  4. 11-05-2013, 12:28 PM #4
    Fotis1991
    Fotis1991 is offline
    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: Macro to highlight date cells based on criteria linked to "today"'s date

    A recorded macro(which of course we can improove-subtracting all the unnecessary lines) works in less than 5 seconds.
    Please Login or Register  to view this content.
    Another one that will delete the cf when you'll not need, is possible to done.
    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.
    Reply With QuoteRegister To Reply
  5. 11-05-2013, 12:45 PM #5
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Macro to highlight date cells based on criteria linked to "today"'s date

    Maybe:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 11-05-2013, 12:47 PM #6
    Gti182
    Gti182 is offline
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    459

    Re: Macro to highlight date cells based on criteria linked to "today"'s date

    John that is perfect, works immediately. Still having the problem however of the first row losing the heading highlighting?
    Reply With QuoteRegister To Reply
  7. 11-05-2013, 12:49 PM #7
    Gti182
    Gti182 is offline
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    459

    Re: Macro to highlight date cells based on criteria linked to "today"'s date

    thanks fotis, i tried your code but got a debug error.
    Reply With QuoteRegister To Reply
  8. 11-05-2013, 12:51 PM #8
    Fotis1991
    Fotis1991 is offline
    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: Macro to highlight date cells based on criteria linked to "today"'s date

    A better version..

    Please Login or Register  to view this content.
    And..

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 11-05-2013, 12:54 PM #9
    Gti182
    Gti182 is offline
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    459

    Re: Macro to highlight date cells based on criteria linked to "today"'s date

    Fotis there's a runtime error 'S' invalid procedure call or argument on this bit of code:

    "Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=AND(N2>0;N2<TODAY()+14)""
    Reply With QuoteRegister To Reply
  10. 11-05-2013, 12:57 PM #10
    Fotis1991
    Fotis1991 is offline
    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: Macro to highlight date cells based on criteria linked to "today"'s date

    ....................
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 11-05-2013, 12:58 PM #11
    Gti182
    Gti182 is offline
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    459

    Re: Macro to highlight date cells based on criteria linked to "today"'s date

    Fotis still get the same error using your file. Do you think the difference in excel versions is a problem? I'm using 2010
    Reply With QuoteRegister To Reply
  12. 11-05-2013, 01:02 PM #12
    Fotis1991
    Fotis1991 is offline
    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: Macro to highlight date cells based on criteria linked to "today"'s date

    check this one pls.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  13. 11-05-2013, 01:17 PM #13
    Fotis1991
    Fotis1991 is offline
    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: Macro to highlight date cells based on criteria linked to "today"'s date

    Quote Originally Posted by Gti182 View Post
    Fotis still get the same error using your file. Do you think the difference in excel versions is a problem? I'm using 2010
    No. This is not a problem. Check again the sample in post#12 pls and let me know.
    Reply With QuoteRegister To Reply
  14. 11-05-2013, 01:18 PM #14
    JOHN H. DAVIS
    JOHN H. DAVIS is offline
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Macro to highlight date cells based on criteria linked to "today"'s date

    Quote Originally Posted by Gti182 View Post
    John that is perfect, works immediately. Still having the problem however of the first row losing the heading highlighting?
    My mistake, try:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  15. 11-06-2013, 06:04 AM #15
    Gti182
    Gti182 is offline
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    459

    Re: Macro to highlight date cells based on criteria linked to "today"'s date

    great, thanks everyone for the help!
    Reply With QuoteRegister To Reply
  16. 04-08-2014, 06:31 AM #16
    Gti182
    Gti182 is offline
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    459

    Re: Macro to highlight date cells based on criteria linked to "today"'s date

    I've amended post #14 to extend to 60 days ahead (previously 14 days ahead) but now getting a runtime error 13 type mismatch on below red bit of code. Can't quite figure it out, any help?


    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  17. 04-08-2014, 09:18 AM #17
    Gti182
    Gti182 is offline
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    459

    Re: Macro to highlight date cells based on criteria linked to "today"'s date

    ah doesn't appear that i can "unsolve" the post possibly start a new one
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Macro to highlight date cells based on criteria linked to "today"'s date
    By Gti182 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-04-2013, 10:59 AM
  2. Macro-Highlight cells in column D if the date format is other than "mmm-dd-yyyy hh:mm:ss"
    By mani88 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-13-2013, 06:25 AM
  3. Help adding VBA code to existing macro to select "today's" date
    By kennected in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-18-2012, 09:21 PM
  4. Macro to File/Save as "Numbers as of [today's date]" on desktop
    By duugg in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-26-2009, 11:38 AM
  5. Macro to rename a worksheet to "Numbers as of [+today's date]"
    By duugg in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-17-2009, 03:28 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1