Searching Worksheets for related items and then highlighting matching row

By lucy_okia ·
I am writing a Macro in Excel that will compare two Worksheets within the same Workbook to find matching entries.

Step 1


Step 2
Use the Selection from SheetName1 and Search SheetName2 for matching entries.
If found in any cell then
highlight the Row.

I need to somehow modifiy the code below to find and highlight all matching entries in SheetName2 in one run:

Cells.Find(What:=("???") After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.Interior.ColorIndex = 6

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Simpler way.

by dryflies In reply to Searching Worksheets for ...

Just use conditional formatting comparing the corresponding cells on sheet one and sheet two?

Collapse -

Conditional formatting

by lucy_okia In reply to Simpler way.


I tried using Conditional formatting at first but received an error messaging along the lines that I cannot reference another worksheet as part of the condition.

This is way I thought it best to write a macro that can handle the workload.

Collapse -

OK, let's do an end around on the system.

by dryflies In reply to Conditional formatting

create a column in the current worksheet indicating the type of formatting you want to use based on the values from the other worksheets. then use it to define the conditional formatting.

Related Discussions

Related Forums