Number formatting

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

Hi

I am currently using the following number format for a
cell

#,##0 ;[Red]-#,##0

which gives the following result

-32,216

What I want is a number format that shows the number out
to 2 decimal places but only if there are decimal places
in the number. For example if the number was 32216.25 I
would want the number to be displayed as -32,216.25 But
if the number was -32216 I would want it displayed as -
32,216 and not -32,216.00

Is this possible and easy to do?

Thanks

PS The General format is not what I want
 
Kevin

I don't think you can achieve exactly what you want. You could change the
format to #,##0.## ;[Red]-#,##0.## but then -32,216 would display
as -32,216. with a point after the 6

You could maybe get away with a worksheet change event macro to cope with
the situation:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsNumeric(Target) Then Exit Sub
If Target.Value = Int(Target.Value) Then
Target.NumberFormat = "#,##0;[Red]-#,##0"
Else
Target.NumberFormat = "#,##0.##;[Red]-#,##0.##"
End If
End Sub


Regards

Trevor
 
Back
Top