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

Hi Sean,

It sounds like conditional formatting would be the easiest solution:

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

Hope that helps,

Sarah

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


"Sean" wrote in message
...
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

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"Sean" wrote in message
...
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

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)



"Sean" wrote in message
...
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

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