Jan has attended:
Excel Advanced course
Conditional format
Hi
How can I conditionally format a cell based on the value returned in another cell?
RE: Conditional format
You would need to link the two cells by a formula.
You could use an IF statement, which would return a TRUE or FALSE valuem, which you could then conditionally format.
Otherwise you could use a simple =cellreference .
Alternatively you may want to enter a value in the cell, and then compare it to other cell, by using the conditional format.
Let me know how you get on.
Richard
RE: Conditional format
Hi Richard
Sorry am I missing the point?
How do I conditionally format within the formula?
I want to conditionally format a cell based on the value of another. If a negative number is returned then the format of the other cell is in red font or green font if positive nuber returned in the other cell.
RE: Conditional format
Hi Jan,
You should be able to do this.
These instructions are for Excel XP, but should work in 2003 and 2007 as well (menu options may be different).
Select the target cell (the one you want to be coloured).
Go to Format -> Conditional Formatting...
In the dialogue box that appears you can set 3 conditions. The first one appears for you, you just have to fill in the conditions.
Ensure the first drop down is set to 'Formula is'.
Now in the formula box, type equals (=) and then the source cell (the one you want to control the colour).
Then type greater than (>) and then 0.
So your formula will look like this:
=B1>0
Now press the 'format' button, and select which colour you want (ie. green). Press 'Okay' to close colour box.
That's the first one done. Now for the 'red' colour when your cell value.
Click the 'Add >>' button at the bottom. A new condition row appears in the dialogue box. Again, select 'Formula is' from the drop down. For the formula, we want almost the same, but it's Less Than zero this time.
=B1<0
If you want 0 values to be red as well, just add in the Less Than or Equal To condition, like this:
=B1<=0
Press the 'format' button, and select Red colour. Press 'Okay' to close colour dialogue box.
Now test it out! Press 'Okay' to close the Conditional Formatting dialogue box, and change the B1 value (or whatever your source cell is). The target cell's colour should change according to the condition. In your case Green for positive numbers and Red for negative numbers.
If your target cell is within a column and you want all of the rows to behave the same way, the last thing to do is use the 'Format Painter' to copy the conditional formatting to all the other cells in your column.
(ie. click the target cell, click 'format painter' button on toolbar, then drag mouse over other target cells, or select entire column).
Hope this helps you Jan. Do let me know if you get stuck. I've also attached a screenshot of the file I was working on when writing this reply.
Regards, Rich
conditional-formatting.gif
