Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highlight Cell Based Upon Referenced Cell Data | Excel Worksheet Functions | |||
I can't see Highlight cell in Find & Replace Cell | Excel Discussion (Misc queries) | |||
click on one cell to find and highlight a related cell? | Excel Discussion (Misc queries) | |||
Highlight Cell | Excel Discussion (Misc queries) | |||
Highlight cells with ctrl-click but only un-highlight one cell | Excel Discussion (Misc queries) |