I would like to accomplish conditional decimal formatting in a cel

[Tim Richards]

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.

 

[Bob Phillips]

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)

 

[Ron Rosenfeld]

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.

If I understand you correctly, if your value is 1 or greater, you want it
formatted as #.00; and if it is less than one, you want if formatted as .0000

So you can use the custom format:

Format/Cells/Number/Custom Type:

[<1].0000;#.00


--ron

 

[Tim Richards]

Thanks to both of you (Bob, Ron),
I appreciate your quick response.
I chose to go with Ron's fix as it was quick and easy.
It works perfect!
Thanks again to both of you for your insite.
Maybe one day i'll get good enough at this to answer some questions myself
and return the favor to someone else!

Tim

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.

If I understand you correctly, if your value is 1 or greater, you want it
formatted as #.00; and if it is less than one, you want if formatted as .0000

So you can use the custom format:

Format/Cells/Number/Custom Type:

[<1].0000;#.00


--ron

 

[Bob Phillips]

Makes sense Tim. Mine was a bit more generic than Ron's as I thought you
wanted to format anything with a decimal portion, such as 85.0, didn't
understand that it would only apply to numbers less than 1.

--
HTH

Bob Phillips

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

Thanks to both of you (Bob, Ron),
I appreciate your quick response.
I chose to go with Ron's fix as it was quick and easy.
It works perfect!
Thanks again to both of you for your insite.
Maybe one day i'll get good enough at this to answer some questions myself
and return the favor to someone else!

Tim

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.

If I understand you correctly, if your value is 1 or greater, you want it
formatted as #.00; and if it is less than one, you want if formatted as ..0000

So you can use the custom format:

Format/Cells/Number/Custom Type:

[<1].0000;#.00


--ron