Find occurance of text in a column

Select B2

Conditional Formatting
New Rule
Use a formula to determine...

=NOT(ISNA(VLOOKUP(A1,B$1:B$8,1,0)))

Format as green
Use Format painter (paintbrush icon) to copy to other cells

You would need to use Conditional Formatting to do that.

Select column A, go CF, New Rule, and choose Use a formula to determine...

Enter formula:

=SEARCH(C$2,A2)>=1

Select formatting in click Ok.

In CF rules

Formula:

=COUNTIF($B2,"*"&$C$2&"*")>=1

How can you use Conditional Formating.

--In Excel 2007 and 2010, Conditional Formatting is in the Styles group on the Home tab. In Excel 2003, Conditional Formatting is on the Format menu. See here how you can work using CF.

Select b2:b8
and use below formula in condition format and change format as per your requirement
=ISNUMBER(SEARCH(" "&C$2&" "," "&B2&" "))*1

I looked at this a little differently. Seeing that there are a few hundred names to be considered, I assumed that there would be maybe a few hundred unique numbers or words to be compared with.

For workbook 2 I used this formula to draw the names from workbook 1 that matched the keywords or numbers in workbook 2 with the results entered in column H

Formula:

=IFERROR(IF(G2="","",INDEX($B$2:$B$8,MATCH("*"&G2&"*",$A$2:$A$8,0))),"")

I then used this formula in conditional formatting to highlight the names that matched the keywords or numbers in workbook 2

Formula:

=COUNTIF($H$2:$H$8,A2)>0