Highlight a cell.
 

Hi,

I have a column with a range of numbers. I have a total of about 600+
accounts on Column A and on Column B I have a three digit code. If any
account has the following codes
(044,039,108,111,125,166,200,309,310,338,339,341,3 46,526,587,710,787,789,889,
and 948) I would like for the cell to automaticaly flagg the cell red. How
would I enter this in conditional formating?

Thanks

Highlight a cell.
 

=COUNTIF($B$1:$B$20,$A1)=1


"Oscar" wrote:

Hi,

I have a column with a range of numbers. I have a total of about 600+
accounts on Column A and on Column B I have a three digit code. If any
account has the following codes
(044,039,108,111,125,166,200,309,310,338,339,341,3 46,526,587,710,787,789,889,
and 948) I would like for the cell to automaticaly flagg the cell red. How
would I enter this in conditional formating?

Thanks

Highlight a cell.
 

List the codes you want to flag in a range of cells. Let's assume this range
is X1:X20.

Select the range of cells you want to highlight. Let's assume this range is
B1:B600
Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=MATCH(B1,X$1:X$20,0)
Clik the Format button
Select the style(s) desired
OK out

--
Biff
Microsoft Excel MVP


"Oscar" wrote in message
...
Hi,

I have a column with a range of numbers. I have a total of about 600+
accounts on Column A and on Column B I have a three digit code. If any
account has the following codes
(044,039,108,111,125,166,200,309,310,338,339,341,3 46,526,587,710,787,789,889,
and 948) I would like for the cell to automaticaly flagg the cell red. How
would I enter this in conditional formating?

Thanks

Highlight a cell.
 

Assuming your 3-digit codes are text, then try this for your Conditional
Formatting formula...

=ISNUMBER(MATCH(B1,{"044","039","108","111","125", "166","200","309","310","338","339","341","346","5 26","587","710","787","789","889","948"},0))

If they are actually numbers formatted to have leading zeroes, then try this
instead...

=ISNUMBER(MATCH(B1,{44,39,108,111,125,166,200,309, 310,338,339,341,346,526,587,710,787,789,889,948},0 ))

--
Rick (MVP - Excel)


"Oscar" wrote in message
...
Hi,

I have a column with a range of numbers. I have a total of about 600+
accounts on Column A and on Column B I have a three digit code. If any
account has the following codes
(044,039,108,111,125,166,200,309,310,338,339,341,3 46,526,587,710,787,789,889,
and 948) I would like for the cell to automaticaly flagg the cell red. How
would I enter this in conditional formating?

Thanks

Highlight a cell.
 

What if I want to the same for another column but this time instead of being
a list of numbers its only one specific number?

Thanks

"Teethless mama" wrote:

=COUNTIF($B$1:$B$20,$A1)=1


"Oscar" wrote:

Hi,

I have a column with a range of numbers. I have a total of about 600+
accounts on Column A and on Column B I have a three digit code. If any
account has the following codes
(044,039,108,111,125,166,200,309,310,338,339,341,3 46,526,587,710,787,789,889,
and 948) I would like for the cell to automaticaly flagg the cell red. How
would I enter this in conditional formating?

Thanks

Highlight a cell.
 

Can't use array constants in CF

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
Assuming your 3-digit codes are text, then try this for your Conditional
Formatting formula...

=ISNUMBER(MATCH(B1,{"044","039","108","111","125", "166","200","309","310","338","339","341","346","5 26","587","710","787","789","889","948"},0))

If they are actually numbers formatted to have leading zeroes, then try
this instead...

=ISNUMBER(MATCH(B1,{44,39,108,111,125,166,200,309, 310,338,339,341,346,526,587,710,787,789,889,948},0 ))

--
Rick (MVP - Excel)


"Oscar" wrote in message
...
Hi,

I have a column with a range of numbers. I have a total of about 600+
accounts on Column A and on Column B I have a three digit code. If any
account has the following codes
(044,039,108,111,125,166,200,309,310,338,339,341,3 46,526,587,710,787,789,889,
and 948) I would like for the cell to automaticaly flagg the cell red.
How
would I enter this in conditional formating?

Thanks

Highlight a cell.
 

Heh-heh... that's what I get for working up True/False formulas directly on
the worksheet and then posting my findings without thinking about the
original question. Thanks for the reminder.

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
Can't use array constants in CF

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
Assuming your 3-digit codes are text, then try this for your Conditional
Formatting formula...

=ISNUMBER(MATCH(B1,{"044","039","108","111","125", "166","200","309","310","338","339","341","346","5 26","587","710","787","789","889","948"},0))

If they are actually numbers formatted to have leading zeroes, then try
this instead...

=ISNUMBER(MATCH(B1,{44,39,108,111,125,166,200,309, 310,338,339,341,346,526,587,710,787,789,889,948},0 ))

--
Rick (MVP - Excel)


"Oscar" wrote in message
...
Hi,

I have a column with a range of numbers. I have a total of about 600+
accounts on Column A and on Column B I have a three digit code. If any
account has the following codes
(044,039,108,111,125,166,200,309,310,338,339,341,3 46,526,587,710,787,789,889,
and 948) I would like for the cell to automaticaly flagg the cell red.
How
would I enter this in conditional formating?

Thanks

Highlight a cell.
 

I've done the exact same thing (as recently as last week!).

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
Heh-heh... that's what I get for working up True/False formulas directly
on the worksheet and then posting my findings without thinking about the
original question. Thanks for the reminder.

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
Can't use array constants in CF

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
Assuming your 3-digit codes are text, then try this for your Conditional
Formatting formula...

=ISNUMBER(MATCH(B1,{"044","039","108","111","125", "166","200","309","310","338","339","341","346","5 26","587","710","787","789","889","948"},0))

If they are actually numbers formatted to have leading zeroes, then try
this instead...

=ISNUMBER(MATCH(B1,{44,39,108,111,125,166,200,309, 310,338,339,341,346,526,587,710,787,789,889,948},0 ))

--
Rick (MVP - Excel)


"Oscar" wrote in message
...
Hi,

I have a column with a range of numbers. I have a total of about 600+
accounts on Column A and on Column B I have a three digit code. If any
account has the following codes
(044,039,108,111,125,166,200,309,310,338,339,341,3 46,526,587,710,787,789,889,
and 948) I would like for the cell to automaticaly flagg the cell red.
How
would I enter this in conditional formating?

Thanks