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

Asked By ZoeMcGo on 09-Feb-09 02:50 PM
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
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?




Pecoflyer replied on 09-Feb-09 03:21 PM
ZoeMcGoe;221586 Wrote:

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/showthread.php?t=61084
Pete_UK replied on 10-Feb-09 01:50 AM
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:

=3DD2=3D"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:

=3DD2=3D"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:

=3DD2=3D"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=A0pm, ZoeMcGoe <ZoeMc...@>
ch
text
ely
ck of
ShaneDevenshir replied on 09-Feb-09 03:28 PM
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
Luke replied on 09-Feb-09 03:30 PM
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!*
Pecoflyer replied on 09-Feb-09 03:34 PM
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/showthread.php?t=61066
Pete_UK replied on 10-Feb-09 01:51 AM
What does this mean?

Pete

=3D14
66