Conditional Format based on value in other cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ainbinder - 14 Nov 2007 17:45 GMT

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

Reply to this Message

Bernard Liengme - 14 Nov 2007 18:03 GMT

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

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> 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
[quoted text clipped - 8 lines]
>
> Adam

Reply to this Message

Max - 15 Nov 2007 03:31 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

ainbinder - 15 Nov 2007 16:52 GMT

On Nov 14, 10:03 am, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:

> Instructions for XL2003 (XL 2007 is similar)
> Click on B1
[quoted text clipped - 27 lines]
>
> - 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

Reply to this Message

CLR - 15 Nov 2007 19:58 GMT

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

> 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
[quoted text clipped - 8 lines]
>
> Adam

Reply to this Message

Rate this thread: