Results 1 to 9 of 9

Conditional formatting IF cell CONTAINS x

This is a discussion on Conditional formatting IF cell CONTAINS x within the Excel Questions forums, part of the Question Forums category; Hi, I'm trying to apply conditional formatting to a cell when it contains "RTW" Doing it on a column of ...

  1. #1
    gj
    gj is offline
    New Member
    Join Date
    Jul 2004
    Location
    Vancouver BC
    Posts
    6

    Default Conditional formatting IF cell CONTAINS x

    Hi,

    I'm trying to apply conditional formatting to a cell when it contains "RTW" Doing it on a column of cells where a cell ONLY contains "RTW" is easy, but if the cell also contains other words such as Europe, Africa etc, then I'm stumped.

    Sample cells (straight copy and pasted)
    -----------
    RTW
    "RTW Africa Europe"
    Africa
    Europe
    Asia
    -----------

    The second row contains three values, separated by tabs. That's the hard one. Of course there are other similar cells.

    Anyone have any ideas?

    Thanks for any help!

    Grant

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    10,988

    Default Re: Conditional formatting IF cell CONTAINS x

    One way, assuming the starting cell is A1 (modify if not)...CF formula is:
    =NOT(ISERROR(FIND("RTW",A1)))

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: Conditional formatting IF cell CONTAINS x

    Variation on a theme...

    =FIND("RTW",A1&"RTW")< LEN(A1)
    Windows XP Pro/Office 2003 SP2

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    10,988

    Default Re: Conditional formatting IF cell CONTAINS x

    Just thinking about operator reductions, Mark would this be subject to failure:
    =COUNTIF(A1,"*RTW*")

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: Conditional formatting IF cell CONTAINS x

    Quote Originally Posted by Tom Urtis
    Just thinking about operator reductions, Mark would this be subject to failure:
    =COUNTIF(A1,"*RTW*")
    Looks good to me.
    Windows XP Pro/Office 2003 SP2

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Conditional formatting IF cell CONTAINS x

    Hi Guys:

    Using the formula ...

    =FIND("RTW",A1)

    for Conditional Formatting should work as depicted in the following ...

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2002 XP : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    RTW***
    2
    Africa*RTW***
    3
    Europe***
    4
    RTW*Asia***
    Sheet1 (2)*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  7. #7
    gj
    gj is offline
    New Member
    Join Date
    Jul 2004
    Location
    Vancouver BC
    Posts
    6

    Default Re: Conditional formatting IF cell CONTAINS x

    Awesome!

    Thanks for all the effort guys, the last one by Yogi works a treat.

    Only one question: I tried to specify a range of named celss e.g.

    =FIND("RTW",Begin:End) and it doesn't work. No highlighting at all.

    Similarly, specifying A2 as the first cell doesn't work either. Actually it sort of does - it highlights the cell ABOVE the desired cell nicely. I'm mystified.

    =FIND("RTW",A2)

    The perfect answer would include the range? begin:end but this one works, so I'll use it!

    range: first cell=a2, named begin, last cell = a20, named end

    Thanks, Grant

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Conditional formatting IF cell CONTAINS x

    Hi Grant:

    To apply Conditional Formatting to a range of cells, select the cells, and in the formula for Conditional Formatting use reference to the ActiveCell, and the Conditional Formatting will apply to all the cells in the range. I hear you -- it is a mouthful of a statement, but it is easier to apply than to state it in words like this.

    I hope this helps -- if you need to discuss this further, please post back and then let us take it from there!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  9. #9
    gj
    gj is offline
    New Member
    Join Date
    Jul 2004
    Location
    Vancouver BC
    Posts
    6

    Default Re: Conditional formatting IF cell CONTAINS x

    Hi,

    shoulda figured that part out myself - I WAS selecting the range and then applying the formatting, but the A1 confused me, and made me think that the range needed to be specified in the formula. A read of the find function clarifies

    thanks!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com