Excel Help re Conditional Format

PermaChanged · 7 Apr 2010 at 14:19

Hi all,

This is probably a very basic problem which I'm over thinking but I have a column in excel with the cells formatted for currency, so if they are negative they are red, positives are black.

What I want is the rest of the row to be the same colour as the amounts, so the whole row where there is a negative balance the text needs to be red, positive is black.

Hope you can help!

Sweey · 7 Apr 2010 at 14:49

Lets assume for a moment that A1 is the cell containing the negative value.

Click on A1, hold down shift and move across to cell E1 (or hold down SHIFT and click on Cell E1). Go to Conditional Formatting, select "Formula Is", type in $A1<0 and select a background colour to change all of the cells to.

This should work and apologies if I'm over-simplifying the instructions.

PermaChanged · 7 Apr 2010 at 15:13

Thanks Sweey, that has pointed me in the right direction. But what happens when I need it to carry on all the way down the sheet? Do I need to make a conditional format for every row?

fretted · 7 Apr 2010 at 15:26

you can just highlight the columns, you want to apply before putting in the formula.

PermaChanged · 7 Apr 2010 at 15:37

It's not working like that unfortunately.

Say if A1 is my currency value and B1 is the cell I want to change colour I use the conditional format in B1 =$A$1<0 Red =$A$1>0 Black.

My next currency is in A2 so I need a different conditional format in B2, =$A$1<0 Red =$A$2>0 Black.

I can't use =$A$1:$A$100 though, it's just not working.

Sweey · 7 Apr 2010 at 16:50

Instead of $A$1, just type $A1. That way as you copy and paste cells it will always look at column A but will also update the row reference. $A$1 means it will always look at cell A1 no matter what.

Sorry for the slow reply, off work ill and was in bed feeling like ****.