conditional formatting or VB

[Brian]

I have a spreadsheet with text, dates and numbers. I want to change the
color of the cell if a specific word is present within the cell. For
example. Mr Smith's Retirement. Conditional formatting only works if the
only word in the cell is exactly retirement. I want the cell to turn green
if the word retirement is present. Need about 4-5 differnt colors for
specific words.

thanks

 

[Don Guillett]

4-5 different colors indicates a need for a macro. Have a look in the vba
help index for SELECT CASE.

 

[ryguy7272]

Don, is right, you can only do this with a macro. Take a look at these
examples, and modify it to suit your specific needs:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=90
http://www.ozgrid.com/forum/showthread.php?t=59414
http://www.pcreview.co.uk/forums/thread-1739161.php



Regards,
Ryan---

 

[Dave Peterson]

If you're using xl2007, you get more than the 3 conditional formats that you get
in xl2003 (and before).

And you can use:
formula is: =isnumber(search("retirement",A1))
or
formula is: =isnumber(find("retirement",A1))
or
formula is: =countif(a1,"*retirement*")>0
(where A1 is the activecell)

(=search() and =countif() are not case sensitive. =Find() is case sensitive.)