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?

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?

Tom Ogilvy wrote:
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:


Thanks. I had sort of homed in on this idea by looking at
http://www.cpearson.com/excel/events.htm
As a test I wrote:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "C1" Then
Application.EnableEvents = False
Range("C3").Value = "Changed"
Application.EnableEvents = True
End If
End Sub

If I apply this to anew worksheet in the existing workbook it works. If I
apply it to my existing worksheet it doesn't. I'm not too hot on debugging
VB functions, but if I introduce a syntax error into the code (a rather
crude debugging method!), an error message ccurs when I change cell C1, so
the event is clearly being detected, but nothing is written into cell C3
even with correct(?) code.

Any clues as to where I might be going wrong (or probably more likely, how
do I debug the code?)