highlight values from a range that do no contain values from another range

stanich

New Member
Joined
Jul 30, 2015
Messages
11
hi all,

title may be unclear but what i am looking for is quite straightforward, i believe. I cannot get my head around it, maybe cause it's friday afternoon and my brain has already switched the weekend mode on :)

Is it possible to do some conditional formatting that would highlight the cells in range A1:A16 which do not contain any of the values in ranges B1:B4 and C1:C3 are not present. i.e.:

'Booklets-French' should not be highlighted cause part of the text is present in B1:B4
'Expenses' should not be highlighted cause part of the text is present in C1:C3
'BLUESNAP INC' should be highlighted because no parts of the word can be found in the 2 ranges

Hope i was clear enough

I have tried a bit of COUNTIF and INSUMBER(SEARCH) but they don't seem to do the job.

ps: the range in columns b and c is split on purpose as formula would need to search in multiple ranges

Thanks all!

ABC
BrochurePaperHat
BUSINESS WIREFacebGoog
Christopher -6 featuresGlobalExp
ExpensesBook
FACEBK *AJY927EL62 FB.ME/ADS
Rockefeller Family Office
Stripe refund & Merchant Fees
White Cube/Modern Collection
GOOGLE*SVCSAPPSGODUN Mountain
Out of Pocket Expenses
Paperhat
ADOBE SYSTEMS Adobe SAN JOSE C
APPFIGURES 899000001 NEW YORK
BLUESNAP INC
Global Pay
Booklets-French

<tbody>
</tbody>
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,436
Try this CF formula

=NOT(OR(INDEX(ISNUMBER(SEARCH($B$2:$B$5,A2)),0),INDEX(ISNUMBER(SEARCH($C$2:$C$4,A2)),0)))
 

stanich

New Member
Joined
Jul 30, 2015
Messages
11
Sorry for the late reply, only managed to try it this morning and that wokrs!! exactly what i wanted, you're a star!!
Thanks a lot! :)
 

Forum statistics

Threads
1,148,160
Messages
5,745,123
Members
423,927
Latest member
Pra56

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
Top