|If you do not want to use Macros, you can accomplish the same thing|
with a Defined Name and Conditional Formatting and everything happens
automatically, as soon as you enter your search string.
Sheet 1 cells A1 thru A10 will contain the search values
and will be given a Defined Name:
On Sheet 1
Select cells A1 tru A10
Select Formula Tab
Select Define Name
In the Name Box enter a name like MyList
Make sure the Scope box reads: Workbook
and the Refers To Box shows: =Sheet1!$A$1:$A$10
On Sheet 2 cells A1 thru C10 these cells may contain the search values
and will use Conditional Formatting:
Select cell A1 thru C10
Select Home Tab
Select Conditional Formatting
Select New Rule
Select Use Formula to determine which cells to format
(last selection in the ⊤op box)
In the Formula Box enter the formula:
Select the Format Button
Select the Fill Tab
Select a pretty color
Repeat for Sheet 3
Now when the Search string is entered in any of the cells on Sheet 1, cells in A1 tru A10
if the corresponding string is found on Sheet 2 or 3 in cells A1 thru C10 they will be highlighted.
I would advise against using whole columns as your ranges, as it could slow things down.
Use only those ranges that you will be using.