Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("A2:A20").NumberFormat = "0." & String(Target.Value, "0")
End If
End Sub


Right click on the sheet tab and select View Code

paste in the above:

--
Regards,
Tom Ogilvy


"Andrew" wrote in message
...
I wish to change the number of decimal places displayed in a range of
cells,
dependant on the contents of a specific cell.

A1 2
A2
A3 1.23 } these cells formatted to 2dp
A4 2.34 } because A1 contains 2.
A5 3.45 }
A6
A7 7.02 } formula =SUM(A3:A5)

If I wanted to set the font, border or pattern of the cell I could use
conditional formatting. However, conditional formatting cannot control
the
number of decimal; places displayed.

The values in cells A3:A5 are always the result of a formula (not entered
directly) so I've tried writing a macro function that uses Format( ) to
format a number the way I want. However, this then returns the value as a
string, which means that I can't use the displayed results in a sum
function.

Is there an obvious solution to this?