Solved Search Values Across Excel Sheet and Highlight Any Hits

October 31, 2012 at 10:16:14
Specs: Windows 7
I have a lengthy list of values (20,000 or more) in an Excel column (call that Worksheet 2). I want to search for the occurrence of any of those values in another Worksheet (call that Worksheet 1). If any values from Worksheet 2 are found in Worksheet 1, I want to highlight those values in Worksheet 1.


See More: Search Values Across Excel Sheet and Highlight Any Hits

Report •

October 31, 2012 at 11:06:34
✔ Best Answer
Normally you cannot use Conditional Formatting across sheets.
The work around is to use a Defined Name.

Try this:

On Worksheet 1 select your range of cells you want to match, IE: A1:A10

Select the Formula Tab
Select Define Name

In the Define Name Box, that appears,
type in an appropriate name for your list, like NameList
Make sure the Scope box says Workbook and
at the bottom of the popup box make sure the Refers to
box shows your selected range, IE A1:A10

Click OK

Now goto Worksheet 2

Select the Home Tab

1) Select your cell or range of cells you want to match, IE X1:X10
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:


6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK

Those cells that have a match on Worksheet 1 should now be highlighed
in your pretty color.


Report •
Related Solutions

Ask Question