Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Conditional Formattting (XP/2003)

    Is there a way to use Conditional Formatting to color highlight a cell in a given column that contains a word or group of words?

    For example if column D contains either "On condition" or "Off condition" could they be colored yellow if they contain the word "Off"?

    There may be situations where it is worded "Off situation" or other variations.

    When I import a long list of data it would be helpful to have these cells highlighted.

    Thanks for your assistance.
    Richard Spring

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

    Re: Conditional Formattting (XP/2003)

    Select the cells you want to color conditionally (it can be the entire column if you wish).
    Note the active cell in the selection - in the following, I'll assume it is cell D1.
    Select Format | Conditional Formatting...
    Select Formula Is from the first dropdown list.
    In the box next to it, enter the formula
    <code>
    =ISNUMBER(SEARCH("off ",D1))
    </code>
    Click the Format button.
    Specify the desired formatting.
    OK your way out.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formattting (XP/2003)

    ... from which this cute little gantt chart.
    Thank you, Hans.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Conditional Formattting (XP/2003)

    Thank you Hans, it works like a charm.
    Since it is text that it is searching, why does the formula contain "ISNUMBER"?
    Could you explain the details of how the formula works?

    Thank you,
    Richard Spring

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

    Re: Conditional Formattting (XP/2003)

    SEARCH("off ",D1) returns either the position where "off " is found within the value of D1 (1 if the value starts with it, 2 if there is one other character before it, etc.), or the error #VALUE if "off " doesn't occur.
    The ISNUMBER function returns TRUE if its argument is a number, i.e. if "off " was found, or FALSE if its argument is not a number, for example an error value, i.e. if "off " was not found.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Conditional Formattting (XP/2003)

    Okay - I'm going to need to study that one.

    I have found the more I understand, the less I know.

    Always a opportunity to learn, each and every day.

    Thanks again Hans,
    Richard Spring

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

    Re: Conditional Formattting (XP/2003)

    To get a feeling for how it works, take a look at the attached sample workbook.

  8. #8
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Conditional Formattting (XP/2003)

    Tricky !!! <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    It is interesting with the "off " space after the "off".
    If you remove the space on the "Buss off" example it changes the results.

    Very interesting - learned something new again.

    Thanks Hans,
    Richard Spring

Posting Permissions

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