Number formatting issue in Excel

[asokaw]

Hi

I need to format a number to show 2 decimals when it has decimal numbers, otherwise not

I used #.00 as the format but the problem is when there are no decimals even the .( decimal point ) is shown. how can I
not show that decimal point

eg. 78 is shown as 78. ( I dont need the decimal to be shown )

Thanks

 

[ParamRay]

Try the following code.

You need to select the cell(s) you want to format before running the macro.

Sub Test()
    
    Dim Cell As Range
    
    If TypeName(Selection) <> "Range" Then Exit Sub
    
    For Each Cell In Selection
        If IsNumeric(Cell.Value) Then
            If Int(Cell.Value) <> Cell.Value Then
                Selection.NumberFormat = "0.00"
            End If
        End If
    Next Cell

End Sub

 

[asokaw]

Can I do this without a Macro by using only Formatting cells

 

[Chrisdontm]

What you are asking is to Format the cell under certain conditions, hence use a "Conditional Formatting" Rule.

Highlight the area that you want the rule to apply to,
On the Home tab, click: Conditional Formatting, Slide down to " New Rule ",
Click: " Use a formula to determine which cells to format ",
In the pop up box formula bar type: =$B2=INT($B2)
( In the formula use the cell reference of the top left cell of your highlighted range. )
Then click the " Format... " button, Choose the " Number " tab, Choose " Number " on the left side,
Set the decimal places to zero, and click the little box if you want a comma when having thousands listed,
Then click: " OK ", then click: "OK", then click: " APPLY ",
Then click: " New Rule ",
Click: " Use a formula to determine which cells to format ",
In the pop up box formula bar type: =$B2>INT($B2)
( In the formula use the cell reference of the top left cell of your highlighted range. )
Then click the " Format... " button, Choose the " Number " tab, Choose " Number " on the left side,
Set the decimal places to 2, and click the little box if you want a comma when having thousands listed,
Then click: " OK ", then click: "OK", then click: " APPLY ".

That should get you there...
Have a great week!!!

 

[MarcelBeug]

My suggestion would be to have standard format with 2 decimals as originally posted and only the first rule for conditional formatting from the previous post.
If you have your numbers in a column and want to align the 2 different formats, then you should supply conditional format #_._0_0