How do I format a cell based on the contents of a different cell?

[ZoeMcGoe]

I have a column of values that can have one of four possible values in each
cell - let's say Red, Amber, Yellow or Green. If the cell has the text
"red" in it, I would like to set a fill color of red in the cell immediately
adjacent to the cell containing the actual word "red". I'm having a heck of
a time finding a way to do this. Anyone have any ideas?

 

[Pete_UK]

Suppose your colours (words) are in column D, and you want the cells
in column E to show the appropriate colour. Highlight the cells in
column E (for example from E2 to E50), then click on Format |
Conditional Formatting. In the pop-up you should choose Formula Is
rather than Cell Value Is in the first box, then enter this formula:

=D2="Red"

Then click on the Format button, choose the Patterns tab and click on
Red. Click OK and then Add to set up the second condition. Choose
Formula Is again, and enter this formula:

=D2="Amber"

Then click on the Format button, choose the Patterns tab and click on
Amber. Click OK and then Add to set up the third condition. Choose
Formula Is again, and enter this formula:

=D2="Yellow"

Then click on the Format button, choose the Patterns tab and click on
Yellow. Click OK twice to exit the dialogue boxes. Finally, with those
cells highlighted click on the Fill Colour Icon and choose Green (you
can only have 3 CFs with Excel 2003 or earlier) and conditional
formatting over-rides normal formatting.

Excel will adjust the range references to suit different rows.

Hope this helps.

Pete

 

[Shane Devenshire]

Hi,

The answer is version dependent - in 2003 you will need to VBA write code,
in 2007 you can use Conditional Formatting. 2003 is limited to 3 conditions
and you have 4, 2007 is limited by available memory.

Here is the 2007 solution: Suppose your words are in A1:A1000,

1. select B1:B1000
2. Choose Home, Conditional Formatting, Manage Rules, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:
=A1="Red"
5. Click the Format button and choose a format.
6. Click OK twice
7. New Rule
8. Choose Use a formula to determine which cell to format
9. In the Format values where this formula is true enter the following
formula:
=A1="Green"
10. Click the Format button and choose a format. Repeat for each of your 4
words.
11. Click OK tree times.

 

[Luke M]

You might try goin got Format-Conditional Format. You can either format a
cell based on cell's value, or you can enter a formula resulting in a
true/false result to determine how to format.

Limits at that pre-2007, you can only use 3 conditions. You "create" the
appearance of 4 sometimes by having the default format of cell set to a 4 th
condition. Beyond that, you'd have to use VBA to get more conditions.

 

[Pete_UK]

What does this mean?

Pete