I don't think you can do this with numberformat.
(Am I right this time, Ken??? The last time I answered this way, I was thinking
about your situation--not the question in the post. And Ken (and Ron) corrected
me <<several times!!!>>. <vbg>)
Anyway, if these values are typed in, you could use a worksheet_change event
that resets the numberformat depending on what you typed.
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
If Target.HasFormula Then Exit Sub
If Application.IsNumber(Target.Value) Then
If CLng(Target.Value) = Target.Value Then
'whole number
Target.NumberFormat = "########0_._0_0_0"
Else
'decimal
Target.NumberFormat = "########0.???"
End If
Else
Target.NumberFormat = "General"
End If
End Sub
(Adjust those ##### to include commas if you want them. And change the range to
what you want. I used column A.)
If the values were the results of formulas, you could use the
worksheet_calculate event.
Right click on the worksheet tab that should have this behavior and select view
code. Paste this in and adjust the range/numberformats. Then back to excel to
test it out.
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm