Custom number format problem

I can't think of a custom format to do it.
You could try an event procedure. For example, provided none of the cells in your range to be formatted are the result of formulas :-

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim cell As Range
Dim myRange As Range
Set myRange = Range("A1:A100")

If Not Intersect(Target, myRange) Is Nothing Then
For Each cell In Intersect(Target, myRange)
If IsNumeric(cell) Then
If cell.Value - Int(cell) = 0 Then
cell.NumberFormat = "#,##0"
Else
cell.NumberFormat = "#,##0.##########"
End If
End If
Next
End If
End Sub

Celia

Hi Robert

You will need to use a Conditional Custom format to achieve this, try using:
[>100]#,###.#########,;#.###

Dave

• OzGrid Business Applications

Dave
I can't get this to work. I get the following results :-
(1). For input numbers greater than 100, displayed is the number divided by 1000. Additionally, if the number ends in 000 (say, "5000"), displayed is "5."
(2). For input numbers less than or equal to 100 that are whole numbers (say "99"), displayed is "99."
Would appreciate your advising what I'm doing wrong. Thanks.
Celia

Probably nothing. I only really tried on the numbers shown. Didn't you?

Or maybe your SheetChange event is still running, but disabling events should fix that bit :O)

Anyway I found this since
http://support.microsoft.com/support/excel/content/formats/default.asp

Dave

• OzGrid Business Applications

Dave
I can't get it to work whatever numbers I use and I can't really see why it should.
If you had it working, I think there must be a typo in the format you posted. I've tried various amendments to it without success. Would really like to get it working since I've spent a long time playing around with it.

Thanks for the link (was already familiar with the content thereof). I can't find anything there that indicates how to come up with a custom format that does what is required.
I must be missing something. I can't see how a condition can be set up to do what is required since :-
"The condition in a section of a number format can contain only a comparison operator (<, <=, = >=, >, <>) and a value. The condition cannot contain any other type of formula."

Celia

Celia

I just formated 3 cells as shown then entered the 3 numbers 2123123, 2.111 and 2.1
the result were 2,123.123, 2.11 and 2.1

But having said this I'm assuming that the first example had a typo in it i.e.

2123123 should display as 2,123,123 (not as 2,123,123.)

Was really: 2123123 should display as 2,123.123 (not as 2,123,123.) as Robert stated that:

"but not to display a decimal point unless there are significant digits to the right of the decimal point."

I may have the wrong end of the stick though. Even I don't it certainly does have limitations.

Dave

• OzGrid Business Applications

Dave
We've been trying to arrive at different results. The way I read the question was the way it was actually written : "2123123" should be "2,123,123" and not "2,123,123." But your format produces "2,123.123"
I've admitted defeat - I don't think it's possible.
Celia