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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,434
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,144,702
Messages
5,725,835
Members
422,645
Latest member
Victor9903

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