Conditional Format based on value in other cell

[ainbinder]

Hi! i have an issue where i want to conditionally format a cell (red,
yellow, or green) based on the value in another cell. For example, in
cell B20, the value will be 1 for green, 0 for yellow, -1 for red. I
want the value in that cell to change the color of cell B1, which
contains a complex formula combining two values from other cells (this
is a department expense analysis and that cell contains the variances
in $ and in % terms, so its hard to conditional format because of the
multiple values in the cell).

Can you help? Thanks in advance!

Adam

 

[Bernard Liengme]

Instructions for XL2003 (XL 2007 is similar)
Click on B1
Use Format | Condition Formatting from menu
In the Dialog box, specify Formula Is: =$B$20=1 and use the Format button to
make the font or the background (pattern) to green. Click OK
On the Conditional Formatting dialog, click Add and repeat with
specify Formula Is: =$B$20=0 and set colour to yellow.
Do once more for -1 and red

But it might be possible to directly format B1. What are the 'rules' for
making B20 equal -1,0 and +1?
best wishes

 

[Max]

.. Formula Is: =$B$20=0 and set colour to yellow

For "equal to zero" conditions,
I'd usually throw in an additional check that the cell is not blank
=AND($B$20=0,$B$20<>"")

The above will avert the CF triggering yellow spuriously if B20 is blank (or
if B20 contains a formula which could evaluate to blank: "")

---

 

[ainbinder]

Instructions for XL2003 (XL 2007 is similar)
Click on B1
Use Format | Condition Formatting from menu
In the Dialog box, specify Formula Is: =$B$20=1 and use the Format button to
make the font or the background (pattern) to green. Click OK
On the Conditional Formatting dialog, click Add and repeat with
specify Formula Is: =$B$20=0 and set colour to yellow.
Do once more for -1 and red

But it might be possible to directly format B1. What are the 'rules' for
making B20 equal -1,0 and +1?
best wishes
--
Bernard V Liengme
Microsoft Excel MVPwww.stfx.ca/people/bliengme
remove caps from email








- Show quoted text -

thank you! you are the man! that worked perfectly. the formula i had
in there was =TEXT(B38,"$#,#")&" / "&TEXT(B49,"0.0%"), and B38 and B49
were both lookups in other workbooks. I know i could do a formula
based on this, but the 1,0,-1 may be easier so i can control the
variances in those formulas!

thanks again!
Adam

 

[Guest]

Do the Conditional Formatting in Cell B1 as follows
First condition, Formula is, =B20=0, color yellow
Second condition, Formula is, =B20=1, color green
Third condition, Formula is, =B20= -1, color Red

Vaya con Dios,
Chuck, CABGx3