Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Format Cell Color based on value (2000 sp3)

    Greetings all,

    I have a spreadsheet where I have the user entering date information into different cells. Received and Complete. since this is manual entry, they sometimes enter the complete date as a date before the received date.

    I need to turn the Received date a color if the Complete date is less than the Received date. This also needs to understand if the complete date is blank, to not change the color.

    Example:

    Received date Complete Date
    3-Jun-2005 4-Jun-2005
    4-Jul-2005 1-Mar-2005
    1-Jul-2005

    4-Jul-2005 is greater than 1-Mar-2005, therefore 4-Jul-2005 cell should change color. 1-Jul-2005 is in received, but the complete date is blank. So, cell for 1-Jul-2005 should remain netural.

    Thanks,
    Brad

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Format Cell Color based on value (2000 sp3)

    Say that the received dates are in column A and the complete dates in column B, starting in row 2 (row 1 contains the column headers)
    Select A2:A100 or as far down as needed.
    Select Format | Conditional Formatting...
    Select Formula Is from the first dropdown list.
    Enter the following formula in the box next to it:
    <code>
    =AND(B2<>"",A2>B2)
    </code>
    Click Format... and specify the desired formatting.
    Click OK twice.

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Cell Color based on value (2000 sp3)

    Hans,
    works great! (as always)

    Brad

Posting Permissions

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