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

Conditional Format if cell contains a word
 

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

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

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

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

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

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