Conditional formatting IF cell CONTAINS x

gj

New Member
Joined
Jul 9, 2004
Messages
6
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
One way, assuming the starting cell is A1 (modify if not)...CF formula is:
=NOT(ISERROR(FIND("RTW",A1)))
 
Upvote 0
Just thinking about operator reductions, Mark would this be subject to failure:
=COUNTIF(A1,"*RTW*")
 
Upvote 0
Hi Guys:

Using the formula ...

=FIND("RTW",A1)

for Conditional Formatting should work as depicted in the following ...
Book1
ABCD
1RTW
2AfricaRTW
3Europe
4RTWAsia
Sheet1 (2)
 
Upvote 0
Awesome! :pray:

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
 
Upvote 0
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!
 
Upvote 0
Hi,

:oops: 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. :oops: A read of the find function clarifies :oops:

thanks!
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top