MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Coloring cells "Red" when certain words are entered


Posted by Noir on November 27, 2001 6:14 AM

I purchase computer products from various vendors around the country. We have determined that 3 or 4 vendors should no longer be used because of bad performance, high prices,etc. (More vendors could be added later) Me and 3 other buyers share a common spreadsheet for tracking purchases. I would like to have the cell color "Red" if one of the 3 or 4 vendor names are enetered (eg:Acme company, TSM company,etc).

Can this be done?

Thanks.
-noir


Posted by JJ on November 27, 2001 6:21 AM

Use Conditional Formatting! Format on the Menu Bar, and then Conditional Formatting.

You are ltd to 3 conditions!

Choose = acme or whatever company and then choose a colour from the Format button!

Posted by Noir on November 27, 2001 6:39 AM

Posted by Aladin Akyurek on November 30, 2001 10:20 PM


Make a list of vendors that are in red zone.
Select all of the cells of this list.
Go the Name Box on the Formula Bar.
Type RedList and enter.

Now activate the cell or cells where you have vendor name(s).
Activate Format|Conditional Formatting.
Choose "Formula Is" for condition 1.
Supposing that the cell which you activated is B2, enter as formula:

=ISNUMBER(MATCH(B2,RedList,0))

Activate Format.
Select color red on the Patterns tab.
Click OK.
Click OK.

You're done.

You can have any number of vendors in the RedList.

Aladin

=======

Posted by Noir on December 01, 2001 4:49 AM

Thanks Guy's!!

Posted by IML on December 01, 2001 3:37 PM

Also

Follow exactly the same steps and but use the formula
=COUNTIF(A1,redlist)

Not any better or worst than Aladin's suggested, just what I came up with the board was down.