custom formatting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want my list of data to appear like this:

234.1
1234
1234.2
234

with the decimal points lining up, but with NO Decimal point SHOWING if it's
a whole number.

I tried this format: ????.?

but the decimal point still shows:

234.1
1234.
1234.2
234.

Does anyone know how to make the decimal point go away (for whole numbers),
but still get the column to line up there? Thanks.
 
I don't think you're going to get it using the number format.

But you could use a worksheet event. If you're typing the values in, you can
use something like this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

With Target
If IsNumeric(.Value) Then
If CLng(.Value) = .Value Then
.NumberFormat = "##0_._?"
Else
.NumberFormat = "##0.?"
End If
End If
End With
End Sub

Right click on the worksheet tab that should have this behavior. Select view
code and paste this into the code window.

I used column A in my code.

Change this to what you need.
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top