Highlight Matched Rows

[Guest]

I have a workbook with two (2) sheets on it. On sheet one (1) I have all my
warehouse locations on it down column "A" from rows 3 to 700. On sheet two
(2) in column "A" I enter daily the locations that have been verified! I
want sheet one (1) to highlight in yellow the locations that have been
verified and leave the other ones alone, so I know what needs to be done
still? Any help on this would be grateful...

Thanks,

Sean

 

[co.uk.sarah]

Hi Sean,

It sounds like conditional formatting would be the easiest solution:

http://www.cpearson.com/excel/cformatting.htm

Hope that helps,

Sarah

 

[Rick Rothstein \(MVP - VB\)]

Conditional Formatting (CF) will do what you want, but you need to duplicate
the data on your second sheet into your first sheet because CF can't use a
condition that reference another sheet. So, in some unused column of Sheet1
(for my example below, I'll assume that column is X), put this formula in
its third row....

X3: =Sheet2!A3

and then copy it down to row 700. Now, highlight the entire column and Hide
it (no need to look at the duplicated information on your original sheet).
Next, on Sheet1, click in A3 and drag down to A700 to select the range
A3:A700 keeping A3 the active cell (the active cell is the one that is not
shaded within the selection). Then, click on...

Format/Conditional Formatting...

in Excel's menu bar. Select "Formula Is" in the first drop-down and
Copy/Paste the following formula into the blank field next to the drop
down....

=NOT(ISERROR(MATCH(A1,$X$1:$X$700,0)))

Click on Format button and pick the color you want for the cell from the
Patterns tab on the dialog box that appears. OK your way back to the
spreadsheet. The locations you fill in on Sheet2 should now highlight in the
selected color on Sheet1.

Rick

 

[Rick Rothstein \(MVP - VB\)]

In looking at your posting again, it occurs to me that your data on the
second sheet might not start at row 3... it might start at row 1. Whatever
it is, use that cell as the starting cell for this direction I gave you...

X3: =Sheet2!A3

so, if you data starts on A1 of Sheet2, use this instead...

X3: =Sheet2!A1

Also, I used the limits from my testing spreadsheet in this conditional
equation...

=NOT(ISERROR(MATCH(A1,$X$1:$X$700,0)))

Actually, the $X$1 above should be $X$3 to match the starting cell we placed
the copied data into. While leaving it as $X$1 won't hurt anything (the
MATCH operation will still work correctly), I just thought I would mention
this in case the mis-reference got you to wondering what I was doing.<g>

Rick

 

[Chip Pearson]

Sean,

You can use Conditional Formatting (CF) to do this. First, go to the Insert
menu, choose Name, then Define. In that dialog, enter "Verified" (without
the quotes) in the "Names in workbook" box and enter
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1) in the Refers To text box.
Change Sheet2 to the name of the sheet containing your verified list sheet
and change $A$1 and $A$A to the column with your verified names. Click OK.
This step to create a Defined Name is required because you can't have a CF
rule pointing directly to a range on another sheet, but using a Name that
points to another sheet is allowed.

Next, select cells the cells on sheet1 that contain the names. Open the CF
dialog from the Format menu, change "Cell Value Is" to "Formula Is", and
enter the following formula:

=NOT(ISERROR(MATCH(A1,Verified,0)))

Change A1 to the first cell in your list on sheet2. Then click the Format
button to change the format as desired.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Rick Rothstein \(MVP - VB\)]

This step to create a Defined Name is required because you can't have a CF

rule pointing directly to a range on another sheet, but using a Name that
points to another sheet is allowed.

I keep forgetting this (hence, the "hidden duplicated row" solution I
posted)... seems like a strange restriction that Excel can make this
reference across sheets via a Defined Name, but can't do it if you
substitute in the definition directly.

Rick