Hi there, please help with the following...

Working with two sheets, I have a list of alpha numeric referances in col.
A. I would like the cells in col. A on sheet 1 to show up as a differant
colour if they appear appear in list in col A on sheet 2.

What formula should I use in the conditional formatting?

Many Thanks.

First, go to Sheet2 and select the maximum range in Column A that you ever
expect to fill your list in with (that is, don't be afraid to include blank
cells in the range). Now, click in the Name Box (that is the edit field on
the formula bar to the left of the formula fill-in field) and type in a name
for this range (for this example, call it LookUpRange). Now, go back to
Sheet1 and select the maximum range in its Column A (again, you can include
blanks in order to handle future entries); click

Format/Conditional Formatting....

from Excel's menu bar. Once in there, select Formula Is from the first drop
down and put this formula in the second field...

=ISNUMBER(MATCH(A1,LookUpRange,0))

(where A1 is assumed to be the active cell within the selected range) and
then click the Format button to select your color.

Rick


"Patch" wrote in message
...
Hi there, please help with the following...

Working with two sheets, I have a list of alpha numeric referances in col.
A. I would like the cells in col. A on sheet 1 to show up as a differant
colour if they appear appear in list in col A on sheet 2.

What formula should I use in the conditional formatting?

Many Thanks.

A point I should have made is that in order to have a reference to a range
on a different sheet in Conditional Formatting, that range must be named and
the name must be used in the formula.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
First, go to Sheet2 and select the maximum range in Column A that you ever
expect to fill your list in with (that is, don't be afraid to include
blank cells in the range). Now, click in the Name Box (that is the edit
field on the formula bar to the left of the formula fill-in field) and
type in a name for this range (for this example, call it LookUpRange).
Now, go back to Sheet1 and select the maximum range in its Column A
(again, you can include blanks in order to handle future entries); click

Format/Conditional Formatting....

from Excel's menu bar. Once in there, select Formula Is from the first
drop down and put this formula in the second field...

=ISNUMBER(MATCH(A1,LookUpRange,0))

(where A1 is assumed to be the active cell within the selected range) and
then click the Format button to select your color.

Rick


"Patch" wrote in message
...
Hi there, please help with the following...

Working with two sheets, I have a list of alpha numeric referances in
col.
A. I would like the cells in col. A on sheet 1 to show up as a differant
colour if they appear appear in list in col A on sheet 2.

What formula should I use in the conditional formatting?

Many Thanks.

Thank you Rick that is perfect

"Rick Rothstein (MVP - VB)" wrote:

A point I should have made is that in order to have a reference to a range
on a different sheet in Conditional Formatting, that range must be named and
the name must be used in the formula.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
First, go to Sheet2 and select the maximum range in Column A that you ever
expect to fill your list in with (that is, don't be afraid to include
blank cells in the range). Now, click in the Name Box (that is the edit
field on the formula bar to the left of the formula fill-in field) and
type in a name for this range (for this example, call it LookUpRange).
Now, go back to Sheet1 and select the maximum range in its Column A
(again, you can include blanks in order to handle future entries); click

Format/Conditional Formatting....

from Excel's menu bar. Once in there, select Formula Is from the first
drop down and put this formula in the second field...

=ISNUMBER(MATCH(A1,LookUpRange,0))

(where A1 is assumed to be the active cell within the selected range) and
then click the Format button to select your color.

Rick


"Patch" wrote in message
...
Hi there, please help with the following...

Working with two sheets, I have a list of alpha numeric referances in
col.
A. I would like the cells in col. A on sheet 1 to show up as a differant
colour if they appear appear in list in col A on sheet 2.

What formula should I use in the conditional formatting?

Many Thanks.