Conditional Format based on value in other cell

  • Thread starter Thread starter ainbinder
  • Start date Start date
A

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
 
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
 
.. 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: "")

---
 
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 -
Click to expand...

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