Formatting for variable decimals

[Penfold]

I know with conditional formatting you can change colours and fonts, but is there a way of changing the number of figures after the decimal point. For example, if my number is less than 100 I want two significant figures. If it is greater than 100 but less than 1000 I want one, and for greater than 1000 I don't want anything after the decimal point:
eg 99.99
999.9
9999

Thanks,

Penfold

[Derek]

Hi Penfold

Don't think you can achieve this with conditional formatting. You could try this event macro though:
Right click your sheet tab, left click View Code, then paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value < 100 Then
Target.NumberFormat = "0.00"
Else
If Target.Value < 1000 Then
Target.NumberFormat = "0.0"
Else
If Target.Value >= 1000 Then
Target.NumberFormat = "0"
End If
End If
End If
End Sub

This appears to round up (ie 99.995 becomes 100.00) but may help you.

regards
Derek

[ This Message was edited by: Derek on 2002-03-11 00:51 ]

[Dave Hawley]

Hi


I was beaten to it but here is mine anyway.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strFormat As String
If Target.Cells.Count > 1 Then End
If Not IsNumeric(Target) Then End

Select Case Target
Case Is < 100
strFormat = "0.00"
Case Is < 1000
strFormat = "0.0"
Case Is > 9999
strFormat = "0"
End Select

Target.NumberFormat = strFormat

End Sub

[Penfold]

Thanks Derek, thats almost exactly what I want except that I have a large range of cells which I want to apply this to (rather than one by one). I guess I can use a similar process via VBA to do this or alternatively add an IF(a1<100,trunc(a1,2)...etc) statement to each cell but I was hoping that the magic of conditional formatting would solve all of my problems.

Cheers,

Penfold

[Derek]

Hi again

Paste this code into a standard macro module, select the area you want to change then run this macro. This will format all your existing cells. After this the event macro I gave you before (or the one from Dave Hawley) will format any new entries you make, as you make them.

For Each cell In Selection
If cell.Value < 100 Then
cell.NumberFormat = "0.00"
Else
If cell.Value < 1000 Then
cell.NumberFormat = "0.0"
Else
If cell.Value >= 1000 Then
cell.NumberFormat = "0"
End If
End If
End If
Next
End Sub

regards
Derek