+ Reply to Thread
Results 1 to 5 of 5

Conditional Formatting to Indicate Start Date & End Date

  1. #1
    Registered User
    Join Date
    11-03-2011
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    6

    Question Conditional Formatting to Indicate Start Date & End Date

    Hi All,

    I'm trying to setup a schedule tracker while monitoring contract start and expiry date.
    Previously I received help but I did not understand how the formula used
    "=IFERROR(MATCH($A5&B$4,range1,0),0)>0" and could not get it to work on my entire workbook.

    Sheet1 contains Names, Start Date, End Date
    Sheet2 - SheetX contains Names, cells representing each day in a month

    Please guide me on how to indicate Yellow for Start Date and Orange for End Date using Conditional Formatting. Thank you.
    Attached Files Attached Files
    Last edited by NiTRO-[X]; 11-07-2011 at 11:12 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional Formatting to Indicate Start Date & End Date

    It isn't really clear what exactly you want highlighted.

    If you want to color cell in the monthly sheets based on date entered in Main sheet... then.

    first name the range in Main, select A4:C17 and name it: e.g. DataRange

    Then in Nov11 sheet, select the range from B5:AE18 and apply conditional formatting, Home|Conditional Formatting|New Rule

    Select use a formula to determine which cells to format and enter formula:

    =VLOOKUP($A5,DataRange,2,0)=B$4

    Click Format and choose yellow from Fill tab.

    Click Ok.

    Click New Rule and repeat above with formula:

    =VLOOKUP($A5,DataRange,3,0)=B$4
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    11-03-2011
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Conditional Formatting to Indicate Start Date & End Date

    Thank you for your reply NBVC.
    Yes this is the end result I needed.

    I somewhat understood the your formulas below, but unsure of the underlined ones.
    Highly appreciate it if you could elaborate this for me. Thank you.

    =VLOOKUP($A5,DataRange,2,0)=B$4
    =VLOOKUP($A5,DataRange,3,0)=B$4

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional Formatting to Indicate Start Date & End Date

    The 2 and 3 are column index numbers.. i.e. the column within the DataRange you want to extract. The 0's mean look for exact match...

    Have a look at VLOOKUP in help file (note 0 is equivalent to FALSE, for finding exact match)

  5. #5
    Registered User
    Join Date
    11-03-2011
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Conditional Formatting to Indicate Start Date & End Date

    Along with your above elaboration it was much easier for me to understand when reading on VLOOKUP.
    Thanks again NBVC.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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