Conditional Format if cell contains a word

[conditional format if cell contains 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.


(e-mail address removed)

Thank you in advance,



Matthew Anderson

 

[T. Valko]

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 Format>Conditional 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 (e-mail address removed)> wrote in message
news:[email protected]...

 

[T. Valko]

Ooops! Typo...

Assume C1 is the cell to format
Select cell A1

Should be:

Select cell C1

 

[conditional format if cell contains word]

Thank you Tom, I am going to try this now. I will keep you informed. Thanks
again.

Matthew

 

[conditional format if cell contains 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?

 

[conditional format if cell contains 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]

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"