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?

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

ZoeMcGoe;221586 Wrote:
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?

As you have more than 3 conditions, ( except with XL2007), VBA will be
necessary


--
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=61084

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

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
On Feb 9, 7:50*pm, ZoeMcGoe
wrote:
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?

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

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"ZoeMcGoe" wrote:

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?

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

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ZoeMcGoe" wrote:

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?

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

Threads merged and duplicates deleted


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

What does this mean?

Pete

On Feb 9, 8:34*pm, Pecoflyer wrote:
Threads merged and duplicates deleted

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

Pete_UK;222199 Wrote:
What does this mean?

Pete

On Feb 9, 8:34*pm, Pecoflyer wrote:
Threads merged and duplicates deleted

--
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:'The Code Cage Forums - View Profile: Pecoflyer'
(http://www.thecodecage.com/forumz/me...pecoflyer.html)
View this thread:'How do I format a cell based on the contents of a
different cell? - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=61066)

Hi Pete,
just keeping our forum tidy

Cheers


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