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

[stanich]

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!

A	B	C
Brochure	Paper	Hat
BUSINESS WIRE	Faceb	Goog
Christopher -6 features	Global	Exp
Expenses	Book
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>

 

[AlphaFrog]

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]

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!