Looks like you have 5 different formats based on value.
custom format can handle positiver nad negative so that take care of 2
set custom format to the most likely positive and negative formats (not
necessary but good practice)

use FormatconditionalFormat to assign 3 additional formats if "cell is"
"between" teh 3 other ranges. you are allowed 3 conditional formats per cell

"helen@bgs" wrote:

Hi
I would like to format a range of numbers in a spreadsheet depending on
their individual values. The cell values are linked (e.g. ='Totals in
solution'!E7) to a cell containing an 'if' statement and calculation e.g.
=IF(D7<(D$3*D$4),((-D$3*D$4)*$C7/$B7),D7*($C7/$B7)) on another worksheet
within the workbook. It kicks out values ranging from -30 to 100 which I'd
like to format to 3 sig figs if less than 100 and no d.p. if 100 or greater.
I'd also like to be able to format the negative values to <0.000, <0.00 and
<00.0. I've a custom format that can manage some of this but not all. Any
suggestions greatly appreciated.