+ Reply to Thread
Results 1 to 3 of 3

Enter and highlight data in columns based on other columns

  1. #1
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Question Enter and highlight data in columns based on other columns

    See both worksheets in the attached spreadsheet for more clarification on this macro...


    Hello all,

    I have a worksheet with 20+ columns. For this macro, I only need to focus on 4 of them. However, none of these columns are ever in a fixed position so the macro would need to find them by name and NOT by column position. Here they are...

    1. Vacation Type (will only have a text value of either "Cold" or "Warm")

    2. Vacation Started (will always have a date *x/xx/xxxx)

    3. Vacation Ended (sometimes it will have a date '*x/xx/xxxx' and sometimes it will NOT have a date and will be truly blank)

    4. Number of Days (currently has ALL truly blank cells)

    THIS WHOLE MACRO SHOULD NOT BE CASE SENSITIVE ANYWHERE



    Here's what I would like the macro to do...

    Scenario 1 - for "Cold" values
    Find "Cold" text values in the "Vacation Type" column

    "Cold" values WITH a date in the "Vacation Ended" column
    IF there IS a date in the "Vacation Ended" column in the same row, put the number of days difference between the "Vacation Started" column and "Vacation Ended" column in the "Number of Days" column.

    The amount of days in the "Number of Days" column will determine whether these cells should be highlighted GREY or RED.


    Scenario 1-A
    IF the number of days difference is 7 days or less, highlight the cells in the "Vacation Ended" column and "Number of Days" column RED.

    OR…

    Scenario 1-B
    IF the number of days difference is MORE than 7 days, highlight the cells in the "Vacation Ended" column and "Number of Days" column GREY.

    AGAIN, 1-A & 1-B above are ONLY if the text value in the "Vacation Type" column says "Cold" with some date in the "Vacation Ended" column.

    See both worksheets in the attached spreadsheet for more clarification on this macro...


    Scenario 2 - for "Cold" values
    Find "Cold" text values in the "Vacation Type" column

    "Cold" values WITHOUT a date in the "Vacation Ended" column


    Scenario 2-C
    If there is NOT a date in the "Vacation Ended" column in the same row, put a "-" in the "Number of Days" column and highlight the cells in the "Vacation Ended" column and the "Number of Days" column GREY.

    AGAIN, 2-C Above Is ONLY if the text value in the "Vacation Type" column says "Cold" and has NO date in the "Vacation Ended" column.



    Scenario 3 - for "Warm" values
    Find "Warm" text values in the "Vacation Type" column

    "Warm" values WITH a date in the "Vacation Ended" column

    Scenario 3-D
    If there IS ANY date in the "Vacation Ended" column in the same row, put the number of days difference in the "Number of Days" column and highlight the cells in the "Vacation Ended" column and the "Number of Days" column GREY.

    AGAIN, 3-D above is ONLY if the text value in the "Vacation Type" column says "Warm" with some date in the "Vacation Ended" column.



    Scenario 4 - for "Warm" values
    Find "Warm" text values in the "Vacation Type" column

    "Warm" values WITHOUT a date in the "Vacation Ended" column

    Scenario 4-E
    If there is NOT a date in the "Vacation Ended" column in the same row, put a "-" in the "Number of Days" column and highlight the cells in the "Vacation Ended" column and the "Number of Days" RED.

    AGAIN, 4-E Above Is ONLY if the text value in the "Vacation Type" column says "Warm" and has NO date in the "Vacation Ended" column.


    Wow, It'll be very interesting to see how all you pros come up with this code. I feel like a kid waiting to open up presents!

    Thanks now and thanks later!
    Attached Files Attached Files
    Last edited by duugg; 07-13-2009 at 09:49 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Enter and highlight data in columns based on other columns

    I wanted to mention that I initially had a slightly altered version of this one on another forum.

    Here's the link...


    http://www.mrexcel.com/forum/showthr...77#post1995877


    Thanks

  3. #3
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Enter and highlight data in columns based on other columns

    This has been solved

+ 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