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

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?

Thanks,
Z~

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

Assuming you have a maximum of four possible colors, and ONLY four possible
labels, then conditional formatting should work. Let's say column A will
hold the shaded cells, and column B holds the words "Red", "Amber", "Yellow"
or "Green". First, since conditional formatting only allows three
conditions, you must set all the cells of interest in Column A to a default
color. Let's set them all to green to take care of the "Green" label.

Now, for each of the other colors, set a conditional format that is like the
following:

Cell A2
Select "Format/Conditional Formatting" from the menu
In the drop-down box, select "Formula Is"
In the formula box, type "=IF(B2="Red",1,0)=1"
Click on the "Format..." button and set the fill color to red

Now, click on the "Add..." button and do two more conditional formats for
"Amber" and "Yellow", using the same formula as above.

Finally, select cell A2, copy it to the clipboard, select all the other
cells of interest in column A, select "Paste Special..." and paste the format
from cell A2 into all those other cells. Then see if it works by typing
"Red", "Amber", "Yellow", "Green" into random cells in column B.

HTH,

Eric

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

egun;221637 Wrote:
Assuming you have a maximum of four possible colors, and ONLY four
possible
labels, then conditional formatting should work. Let's say column A
will
hold the shaded cells, and column B holds the words "Red", "Amber",
"Yellow"
or "Green". First, since conditional formatting only allows three
conditions, you must set all the cells of interest in Column A to a
default
color. Let's set them all to green to take care of the "Green" label.

Now, for each of the other colors, set a conditional format that is
like the
following:

Cell A2
Select "Format/Conditional Formatting" from the menu
In the drop-down box, select "Formula Is"
In the formula box, type "=IF(B2="Red",1,0)=1"
Click on the "Format..." button and set the fill color to red

Now, click on the "Add..." button and do two more conditional formats
for
"Amber" and "Yellow", using the same formula as above.

Finally, select cell A2, copy it to the clipboard, select all the
other
cells of interest in column A, select "Paste Special..." and paste the
format
from cell A2 into all those other cells. Then see if it works by
typing
"Red", "Amber", "Yellow", "Green" into random cells in column B.

HTH,

Eric

Eric, is the IF part necessary, I think B2="Red" will be enough ?


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=61066

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

You could be right, but this is how I have always (succesfully) done it. It
won't hurt to experiment with your suggestion!

Eric