course excel training vba london - 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