I NEED NUMBER CUSTOM FORMAT FOR DECIMAL PLACES

I'm fairly certain that there is no way to do what you want to do using the regular "Format Cells" function. Using that function, you may specify different formats for positive, negative, zero values, and text values, but you can't differentiate between whole numbers and decimals there. You will probably need to write a VBA macro that checks the value of the cell and formats according to whether it is a whole number or a decimal.

The closest that I came to what you want, using the Format Cells function is:
#,##0.00000);[Red](#,##0.00000)

But again, that doesn't do what you want for the whole numbers. Let me know if you need more help.

How about a simple UDF ?

Function MyFormat(Rng as Range) as String
If Not IsNumeric(Tmp) Then Exit Function
If Rng - Int(Rng) > 0 then
MyFormat = Format(Rng,"#,##0.00000")
else
MyFormat = Format(Rng,"#,##0")
End If
End Function

This is A STRING, but could be done with a SUB to keep values as numbers.

Juan Pablo G.

Something like this:

Sub MyFormat2()
Dim Cll As Range
For Each Cll In Selection.SpecialCells(xlCellTypeConstants, xlNumbers).Cells
If Cll - Int(Cll) > 0 Then
Cll.NumberFormat = "#,##0.00000"
Else
Cll.NumberFormat = "#,##0"
End If
Next Cll
End Sub

This assumes that the user selects the interest range PRIOR running the macro.

Juan Pablo G.

Current selection code

Sub numMat()
Selection.NumberFormat = "General"
If Selection - Int(Selection) = 0 Then
ActiveCell.NumberFormat = "#,###"
End If
If Selection - Int(Selection) > 0 Then
ActiveCell.NumberFormat = "#,##0.00000"
End If
End Sub

This will test the current selection for a remainder and then format the results accordingly. JSW

JUAN

THE SUBFORMAT2 WORKS GREAT!

THAT'S EXACTLY WHAT I WAS LOOKING FOR.

THANK YOU AND HAPPY YEAR!

KEEP UP THE GREAT JOB!

YANECKC