cell custom format

[mark kubicki]

I would like the cell numeric entry to appear in "( ###)" format (with
parenthesis)
ex:
(9) or (4.5)...

the entry MIGHT have decimals (ex: 4.5); however, for entries that are an
even integer (ex: 9) I want ONLY the WHOLE number without a THE DECIMAL
POINT to show
ex
(9) o.k.
(4.5) o.k.
(9.) not o.k. -note the decimal point

entering this format (###.##) forces a visible decimal point even with whole
numbers


thanks in advance,
mark

 

[Dave Peterson]

I don't think you'll be able to do this with formatting alone.

But you could use an event macro that looks at the value and applies the correct
numberformat.

If you want to try this, rightclick on the worksheet tab that should have this
behavior and select view code. Paste this into the code window.

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 .Value = Int(.Value) Then
.NumberFormat = "0_);(0)"
Else
.NumberFormat = "0.00_);(0.00)"
End If
End If
End With

End Sub

I used column A for my input range. Change this to what you need.

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

You can read more about these kinds of events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm