I'd use:

#,##0

no dots <bg

And you could loop through all the cells that you want in the
worksheet_calculate event. The code will look pretty much like what you have in
the _change event.

If you have trouble post back with what you've got and your question.

Roee wrote:

Thanks.

Actually, some of the values are the result of a formula and some I will
input manually.

As to the format I think I will use this: #,##0.## (2 places is enough)

One more question:
The VB function is working, how do I set myCell.NumberFormat to be "Numeric"
with a 1000 seperator and 0 decimal places?

Thanks,
Roee.

"Dave Peterson" wrote:

Are you changing those values -- or are they the results of a formula?

If you're changing them yourself, you could use a worksheet_change event.

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

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Dim myCell As Range

Set myRng = Intersect(Target, Me.Range("a:a"))

If myRng Is Nothing Then
Exit Sub
End If

For Each myCell In myRng.Cells
If IsNumeric(myCell.Value) Then
If Int(myCell.Value) = myCell.Value Then
myCell.NumberFormat = "General"
Else
myCell.NumberFormat = "#,##0.0000"
End If
End If
Next myCell

End Sub

I only changed the format in column A.

Did you really mean this: #,##0.####?

Roee wrote:

OK, since that is what Sandy suggested I guess this is as close as it gets.

Any chance that conditional formatting or some VB function can solve it?
Something in general tenor of:
if the number is an integer show in "General" formatting
else use #,##0.####

Roee.

"Bob Phillips" wrote:

The you need

#,##0.####

but you will always see the dot.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Roee" wrote in message
...
But I want it to show decimal parts *if they exists*.
I want to see 1000.05 as 1,000.05 (and not 1,000 or 1000.05) and 1,000 as
1,000 (and not 1,000.00).

Using a fixed 0 decimal places or a format of #,##0 does not allow it.

Any suggestions?

Thanks,
Roee.

"Bob Phillips" wrote:

Use a format of #,##0.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Roee" wrote in message
...
Hello,

I would like to use the 1000 sperator but I don't want to specify fixed
decimal places.
For example, if the cell contains the number 300, I want it to show 300
and
not 300.00, but I still wan't to use the 1000 sperator.
How do I do that?

The "General" formatting showes the decimal places like I want (only if
exists) but does not allow 1000 sperator and the "Number" formatting
requires
defining a fixed number of decimal places.

Thanks,
Roee.







--

Dave Peterson


--

Dave Peterson