+ Reply to Thread
Results 1 to 9 of 9

formatting wth 3 conditions

  1. #1
    Registered User
    Join Date
    05-23-2012
    Location
    san francisco, ca
    MS-Off Ver
    Excel 2003
    Posts
    5

    formatting wth 3 conditions

    Want to color a cell (D1) red if it meets 3 conditions...otherwise leave white:
    1. Cell B5 is blank
    2. Due date has passed (due date in cell C5/todays date in cell A1)
    3. Cell D1 contains the following...XXX

    How do I do this?

    Thanks

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: formatting wth 3 conditions

    Hi lookingforanswers,

    Welcome to the forum.
    Try below formula in conditional formatting logic:-

    =AND(B5="",A1>C5,ISNUMBER(SEARCH("xxx",D1)))

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    05-23-2012
    Location
    san francisco, ca
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: formatting wth 3 conditions

    Thanks very much. Works, but have another question.
    In addition to the above formula, which highlights the cell red if those conditions are met, I want to highlight the cell green instead, if these conditions are met:
    1. If B5 contains the word Completed (vs. blank in the above)
    2. If D1 contains XXX

    I figured I could write a similar formula, in this case =AND(B5="Completed",ISNUMBER(search("XXX",D1))), but doesn't seem to be working...??

  4. #4
    Registered User
    Join Date
    05-23-2012
    Location
    san francisco, ca
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: formatting wth 3 conditions

    my formula has the end quote after completed...didn't include above

  5. #5
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: formatting wth 3 conditions

    If you are saying that cell contains..., use below formula:-


    =AND(ISNUMBER(SEARCH("Completed",B5)),ISNUMBER(SEARCH("XXX",D1)))

    Initially I thought that cell has "completed" but the above formula will take care if the cell contains word "completed".. try using this and let me know if this works. Thanks

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  6. #6
    Registered User
    Join Date
    05-23-2012
    Location
    san francisco, ca
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: formatting wth 3 conditions

    That one didn't work. Prob didn't explain well. I'm tracking tasks to be completed. Left hand side of worksheet contains the date task is to be completed. Right had side is a calendar of sorts, with each column representing one week of time. When task is done, the word Completed is entered in the left side. IF not yet completed, it is left blank. I ultimately want to do 3 things:
    1. When I enter a date in the left side, the corresponding cell on the right side (with that date) gets populated with "XXX". I have that done.
    2. If the due date has passed and the task hasn't been completed, the cell (with the XXX) is highlighted red. You helped me with that above and is now working
    3. That same cell noted in point 2 should highlight green if the task has been Completed and there is an XXX in the cell (I want to avoid the whole row highlighting green-or red in the point above-if task has been completed. Only the cell with the XXX.
    This third point is where I'm now having difficulty.
    Hopefully this is a better explanation.

  7. #7
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: formatting wth 3 conditions

    Hi...

    I guess I need to see the sample workbook, please upload and highlight the result which you are looking .. thanks

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  8. #8
    Registered User
    Join Date
    05-23-2012
    Location
    san francisco, ca
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: formatting wth 3 conditions

    my permissions shows that i can't post attachments

  9. #9
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: formatting wth 3 conditions

    Hi lookingforansewers,

    Try contacting the moderators of the forum to help you in posting attachments.. :-

    http://www.excelforum.com/showgroups.php

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

+ 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