Question Locked

Searching Worksheets for related items and then highlighting matching row

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

Step 1

Sheets("SheetName1").Select
Range("B2:B1926").Select
Selection.Copy
Sheets("SheetName2").Select

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
Rows("???").Select
Selection.Interior.ColorIndex = 6

4 replies

by dryflies

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

by lucy_okia

Dryflies

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.

by dryflies

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.

This discussion is closed to new replies.