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!

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Brochure[/TD]
[TD]Paper[/TD]
[TD]Hat[/TD]
[/TR]
[TR]
[TD]BUSINESS WIRE[/TD]
[TD]Faceb[/TD]
[TD]Goog[/TD]
[/TR]
[TR]
[TD]Christopher -6 features[/TD]
[TD]Global[/TD]
[TD]Exp[/TD]
[/TR]
[TR]
[TD]Expenses[/TD]
[TD]Book[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FACEBK *AJY927EL62 FB.ME/ADS[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rockefeller Family Office[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stripe refund & Merchant Fees[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]White Cube/Modern Collection[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GOOGLE*SVCSAPPSGODUN Mountain[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Out of Pocket Expenses[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paperhat[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ADOBE SYSTEMS Adobe SAN JOSE C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]APPFIGURES 899000001 NEW YORK[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BLUESNAP INC[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Global Pay[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Booklets-French[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

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