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. 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. 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. Re: Conditional formatting IF cell CONTAINS x

Variation on a theme...

=FIND("RTW",A1&"RTW")< LEN(A1)

4. Re: Conditional formatting IF cell CONTAINS x

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

5. Re: Conditional formatting IF cell CONTAINS x

Originally Posted by Tom Urtis
Just thinking about operator reductions, Mark would this be subject to failure:
=COUNTIF(A1,"*RTW*")
Looks good to me.

6. 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
 A1 =

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.

7. 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. 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!

9. 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!

Posting Permissions

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