Formula needed for looking up matching text in separate data sets

Hey guys, sorry for the weirdly worded title but basically I need to be able to search for matching text inside one workbook. This is better illustrated in the attached excel file.

In the test example, I need something where if I search for "John", the formula or lookup will highlight all cases of "John" in both sets of data. Ideally I would need an interchangeable formula where the lookup keyword can change, e.g. searching for either, "John", "John Smith" or "[email protected]".

Let me know if this feasible and thanks in advance!

Change the Contents of B1 to run the macro.

To view the macro right click on the sheet name at the bottom of excel and select view code.

Clever Solution GAK67

Awesome suggestions, thanks so much for the help guys!

Quick question GAK67, is there a way to copy the set-up of that cell over to my main worksheet or would I have to replicate it with conditional formatting? If so, would you be be able to give a brief walk through?

Can I explain for you GAK67?

Gak67 selected all the cells in the worksheet by clicking on the grey triangle at the top left of the spreadsheet.

They then clicked on conditional formatting

Insert new rule

Use a formula

And then he entered the formula

Please Login or Register  to view this content.

This code is written as if for the first cell in the range, A1 but will apply to all cells in the range, excel translates the formula for you,
All you need to do is to make sure it works in A1 and it will work similarly in all cells in the range.

The formula searches for the text in cell M1 in cell A1.

Note that M1 is written $m$1, that means that as you move from cell to cell within your range, your formula will always refer to column M, $M and row1 $1.
A1 is written without $'s because you want the row and column to change, for every cell in your range. Because you want to look in every cell in your range.

finally >0, if the search text is found in your cell then the return the position of the search text, which will be greater than zero.

Finally if the formula is true, The fill colour is changed to Green, click on format and fill to do that.l

Hey guys, thanks for all your help, I really appreciate it. However, I was wondering if there was a formula that will highlight all cases of whatever I plug into the formula in the data-set (if I plug in "John" it doesn't matter if "John" is located by itself, within an email, or as part of a first-name/last-name ala "John", "[email protected], or "John Smith").

Some further clarification as to what I need, is there a way to analyze both data sets at once and simply determine if there is a match for the data in both sets?