Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format if cell contains a word
Hello and thank you for reading my questions. Hopefully you can help.
I have a small to medium sized worksheet in EXCEL2000 that contains numbers and descriptions of meat products. For instance; 17cs BEEF TENDERLOIN USDA UNGRADED 717W The tab size spaces are to indicate that there is a new cell. So the above example is 3 cells. What I need to do is turn the cell red if it DOES NOT contain the one of the words of beef, chicken, pork, veal, lamb, goat. Thank you in advance, Matthew Anderson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format if cell contains a word
Ooops! Typo...
Assume C1 is the cell to format Select cell A1 Should be: Select cell C1 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way... List the items in a range of cells, say, X1:X6 Assume C1 is the cell to format Select cell A1 Goto the menu FormatConditional Formatting Select the Formula Is option Enter this formula in the box on the right: =AND(C1<"",SUM(COUNTIF(C1,"*"&$X$1:$X$6&"*"))=0) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "conditional format if cell contains word" <conditional format if cell contains wrote in message ... Hello and thank you for reading my questions. Hopefully you can help. I have a small to medium sized worksheet in EXCEL2000 that contains numbers and descriptions of meat products. For instance; 17cs BEEF TENDERLOIN USDA UNGRADED 717W The tab size spaces are to indicate that there is a new cell. So the above example is 3 cells. What I need to do is turn the cell red if it DOES NOT contain the one of the words of beef, chicken, pork, veal, lamb, goat. Thank you in advance, Matthew Anderson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format if cell contains a word
Thank you Tom, I am going to try this now. I will keep you informed. Thanks
again. Matthew "T. Valko" wrote: One way... List the items in a range of cells, say, X1:X6 Assume C1 is the cell to format Select cell A1 Goto the menu FormatConditional Formatting Select the Formula Is option Enter this formula in the box on the right: =AND(C1<"",SUM(COUNTIF(C1,"*"&$X$1:$X$6&"*"))=0) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "conditional format if cell contains word" <conditional format if cell contains wrote in message ... Hello and thank you for reading my questions. Hopefully you can help. I have a small to medium sized worksheet in EXCEL2000 that contains numbers and descriptions of meat products. For instance; 17cs BEEF TENDERLOIN USDA UNGRADED 717W The tab size spaces are to indicate that there is a new cell. So the above example is 3 cells. What I need to do is turn the cell red if it DOES NOT contain the one of the words of beef, chicken, pork, veal, lamb, goat. Thank you in advance, Matthew Anderson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format if cell contains a word
Im sorry, but I am new to this. It is not a problem, but am I suppose to key
the formula in character by character? No big deal if so How do you know what to use for a formula? Also I clicked on the cell which is C8 and it shows up as $C$8...whys that? "T. Valko" wrote: One way... List the items in a range of cells, say, X1:X6 Assume C1 is the cell to format Select cell A1 Goto the menu FormatConditional Formatting Select the Formula Is option Enter this formula in the box on the right: =AND(C1<"",SUM(COUNTIF(C1,"*"&$X$1:$X$6&"*"))=0) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "conditional format if cell contains word" <conditional format if cell contains wrote in message ... Hello and thank you for reading my questions. Hopefully you can help. I have a small to medium sized worksheet in EXCEL2000 that contains numbers and descriptions of meat products. For instance; 17cs BEEF TENDERLOIN USDA UNGRADED 717W The tab size spaces are to indicate that there is a new cell. So the above example is 3 cells. What I need to do is turn the cell red if it DOES NOT contain the one of the words of beef, chicken, pork, veal, lamb, goat. Thank you in advance, Matthew Anderson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format if cell contains a word
So I got it to work for a single cell thank you. however, I cannot get it to
work over my complete table. The following is my formula. =AND($C$8:$C$80<"",SUM(COUNTIF($C$8:$C$80,"*"&$H$ 8:$H$12&"*"))=0) "T. Valko" wrote: Ooops! Typo... Assume C1 is the cell to format Select cell A1 Should be: Select cell C1 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way... List the items in a range of cells, say, X1:X6 Assume C1 is the cell to format Select cell A1 Goto the menu FormatConditional Formatting Select the Formula Is option Enter this formula in the box on the right: =AND(C1<"",SUM(COUNTIF(C1,"*"&$X$1:$X$6&"*"))=0) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "conditional format if cell contains word" <conditional format if cell contains wrote in message ... Hello and thank you for reading my questions. Hopefully you can help. I have a small to medium sized worksheet in EXCEL2000 that contains numbers and descriptions of meat products. For instance; 17cs BEEF TENDERLOIN USDA UNGRADED 717W The tab size spaces are to indicate that there is a new cell. So the above example is 3 cells. What I need to do is turn the cell red if it DOES NOT contain the one of the words of beef, chicken, pork, veal, lamb, goat. Thank you in advance, Matthew Anderson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format if cell contains a word
Select the entire range C8:C80 starting from cell C8. C8 will be the active
cell. The active cell is the single cell in the selected range that *is not* shaded. Apply the formatting and use this *exact* formula: =AND(C8<"",SUM(COUNTIF(C8,"*"&$H$8:$H$12&"*"))=0) The formula is relative to the active cell. The references will automatically adjust for the other cells in the applied range. -- Biff Microsoft Excel MVP "conditional format if cell contains word" icrosoft.com wrote in message ... So I got it to work for a single cell thank you. however, I cannot get it to work over my complete table. The following is my formula. =AND($C$8:$C$80<"",SUM(COUNTIF($C$8:$C$80,"*"&$H$ 8:$H$12&"*"))=0) "T. Valko" wrote: Ooops! Typo... Assume C1 is the cell to format Select cell A1 Should be: Select cell C1 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way... List the items in a range of cells, say, X1:X6 Assume C1 is the cell to format Select cell A1 Goto the menu FormatConditional Formatting Select the Formula Is option Enter this formula in the box on the right: =AND(C1<"",SUM(COUNTIF(C1,"*"&$X$1:$X$6&"*"))=0) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "conditional format if cell contains word" <conditional format if cell contains wrote in message ... Hello and thank you for reading my questions. Hopefully you can help. I have a small to medium sized worksheet in EXCEL2000 that contains numbers and descriptions of meat products. For instance; 17cs BEEF TENDERLOIN USDA UNGRADED 717W The tab size spaces are to indicate that there is a new cell. So the above example is 3 cells. What I need to do is turn the cell red if it DOES NOT contain the one of the words of beef, chicken, pork, veal, lamb, goat. Thank you in advance, Matthew Anderson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional format cell | Excel Discussion (Misc queries) | |||
word match in string text in cell, color format cell | Excel Discussion (Misc queries) | |||
Conditional Format Row when Value Changes in One Cell | Excel Worksheet Functions | |||
conditional cell format based on cell in same row, previous column | Excel Worksheet Functions | |||
Conditional format (word change) | Excel Worksheet Functions |