Conditional Format if cell contains a word

  • Thread starter conditional format if cell contains word
  • Start date
C

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

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]...
 
C

conditional format if cell contains word

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

Matthew
 
C

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?
 
C

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

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"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top