Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Int(.Value) < .Value Then
.NumberFormat = "#,##0.0000"
Else
.NumberFormat = "#,##0.00"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Tim Richards" wrote in message
...
Whereas a whole number is represented as 85.00 (2 decimals), and less than
a
whole number is represented as .8500(4 decimals).
My excel speadsheet deals with ppm (parts per million) and percent of a
gas
or a gas range. To display my ppm values correctly I require 4 decimals,
however when the same worksheet/cell reflects % it looks like this(
85.0000).
I do not have the available space to accomodate the larger number(plus it
looks terrible!). I tried some of the custom cell formats found in excel,
but
I could not find one that would work. I also tried an IF statement, but
could
not find a way to modify the decimals when excel met the criteria of the
statement.
Any ideas....anyone....anyone.