Conditional formatting one range containing text from another range

**thedeakster** · 09-01-2013, 01:32 PM

Hi all,

I am having a problem with conditional formatting on a range. The first range is the one in which the formatting is being placed, and consists of descriptions, ie. "256276727 Joe Bloggs" or "25627 Joe Bloggs". The second range consists of numbers such as "25627".

What I wish to do is have every cell from range one that has any number from range two appear within it be placed in a certain colour. I have tried numerous formulas such as countif with wildcards, search functions etc, but can't seem to get to the bottom of how to accomplish this, match doesn't work as the cells in range one consist of more text than just second range numericals. Any help would be great! Many thanks!

**mlcb** · 09-01-2013, 03:36 PM

Maybe this

Data Range

	A	B	C
1	Range1		List
2	256276727 Joe Bloggs		25627
3	25627 Joe Bloggs		12345
4	blah 12345 blah
5	bleh 123456

Select A2:A5
Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format

insert this formula
=SUM(--ISNUMBER(SEARCH(" "&MyList&" "," "&A2&" ")))
pick the format you want

Where MyList is a named range (C2:C3 in the example above)