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