I would like to accomplish conditional decimal formatting in a cel
 

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.

I would like to accomplish conditional decimal formatting in a cel
 

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.

I would like to accomplish conditional decimal formatting in a cel
 

On Wed, 23 Aug 2006 16:28:01 -0700, Tim Richards
wrote:

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

I would like to accomplish conditional decimal formatting in a
 

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

"Ron Rosenfeld" wrote:

On Wed, 23 Aug 2006 16:28:01 -0700, Tim Richards
wrote:

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

I would like to accomplish conditional decimal formatting in a
 

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)

"Tim Richards" wrote in message
...
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

"Ron Rosenfeld" wrote:

On Wed, 23 Aug 2006 16:28:01 -0700, Tim Richards
wrote:

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