Conditional Formatting Excel 2003

[lukerees83]

Dear All,

I have been making a document using both excel 2007 and 2010, part of which contains some conditional formatting. However, the final product will be viewed by people using excel 2003, so obviously when I save to 2003 format on my version of Excel of course I get a message warning that my CF won't work.

I'll give you a list of the most important things I have used CF for that I need to keep somehow:

1. In column P if a cell contains a negative number I have chosen to fill it with red, and green for any cell that contains 0 or a positive number. Blank cells should be unfilled.

2. In the next column I have chosen to make the text different colours depending on the value, for example values greater than or equal to 40 will be green, then >= 31 and less than 40 will be orange, etc etc going down to negative values.

Is there any way to do this in 2003 excel?

Many thanks.

 

[lukerees83]

After a little play I have now seen that I can apply some CF to cells, however this appears limited to 3 conditions, is that really all you get?

Secondly, in response to point 1 where it says at the end that blank cells should be left unfilled, there doesn't appear to be a way to do this on 2003's CF.

 

[AlphaFrog]

After a little play I have now seen that I can apply some CF to cells, however this appears limited to 3 conditions, is that really all you get?

Yes

Secondly, in response to point 1 where it says at the end that blank cells should be left unfilled, there doesn't appear to be a way to do this on 2003's CF.

You don't need a CF for unFilled. The cells will only be Filled if the CF is true. If none of the CFs are true, then by default, none of the cells are filled. You may have to change your CF formulas to ignore blanks e.g. =AND(NOT(ISBLANK(P1)),P1>=0) Green

 

[lukerees83]

The problem with my unFilled cells is that they all contain formulas, however the formulas are designed to leave the cell blank if certain other criteria are not met. My CF is working for the cells where the formula has returned a value but when it returns nothing the cells are being filled with colour anyway.

The 2 exact CFs that I put in are:

'if cell value is <=0 fill cell green' and 'if cell value is >0 fill cell red'.

All blank cells (and the one at the top of the column with the heading in it) turn red.

Any thoughts?

 

[AlphaFrog]

Maybe try using the ISNUMBER function...
=AND(ISNUMBER(P1),P1>0)