Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting (202 SPŁ)

    My spread sheet lists enabling works which the subcontractors need to have completed by the builders, making holes in walls etc. Cell A1 contains =TODAY() and displays the date. Column H records and displays the requested completion date, a date some time in the near future. The plan was to use Conditional formatting, - Cell Value Is - Less than - $A$1 - Font colour Red - to turn the text red when the requested date had passed. Unfortunately all text in column H displays red.

    Not sure why this doesn't work since I have used it before successfully. However, I now need the text to display red when the requested date is past but display black if the requested date is past and also if the completed date in column I is past.

    Can this be done using conditional formatting and if so what am I doing wrong? Or will I have to dust off my VBA books?

    Regards

    Graeme

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

    Re: Conditional Formatting (202 SPŁ)

    > I now need the text to display red when the requested date is past but display black if the requested date is past

    Huh? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  3. #3
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (202 SPŁ)

    Sorry. I knew what I meant!

    A requested completion date for the work is entered in column H. Once that date is past its text turns red. When the work has been done the actual completion date is entered in column I. If the work is done prior to the requested date then the requested date text remains black. If the work is done after the requested date then the text turns red after the requested completion date has passed but returns to black once a completion date is entered. The end result is that all overdue works are highlighted but due works and completed works are not.

    I hope that makes more sense!

    Regards

    Graeme

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

    Re: Conditional Formatting (202 SPŁ)

    Try this:
    - Select column H.
    - Select Format | Conditional Formatting...
    - Select "Formula Is" from the first dropdown.
    - Enter the following formula in the box next to it:
    <code>
    =AND(H1<$A$1,ISBLANK(I1))
    </code>
    - Click Format...
    - Specify the formatting you want.
    - OK your way out.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (202 SPŁ)

    With an edit to the formula for row numbers, that works lovely!

    Thanks

    Graeme

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

    Re: Conditional Formatting (202 SPŁ)

    If you select column H, H1 should be the active cell, so no adjustment should be needed. But if it works for you, never mind.

  7. #7
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (202 SPŁ)

    Seems I missed your first instruction and selected the cell instead of the column. Then had to copy all the cells below! Must pay more attention

    However this reply gives me the opportunity to add one more post which upgrades me from New Lounger to Lounger

    Cheers

    Graeme

Posting Permissions

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