VBA How to format to 2 decimal places only if decimals required-CodePudding

I'm writing a macro to format a data file. I have some columns where data will either be a whole number or a long decimal (e.g. 0.6666667). I want to format the decimals to 2 dp, but I don't want the whole numbers to show .00 decimals. How can I do this?

I saw a similar question elsewhere with this code as an answer, but this leaves a decimal point after a whole number (5. rather than 5):

Range("Q8:Q500").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(Q8-INT(Q8))>0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
     Selection.NumberFormat = "[=0]0;.##"
    Selection.FormatConditions(1).StopIfTrue = False

I also tried writing an if statement to format based on whether there is a decimal point present, but this didn't work either:

For Each Cell In Columns("Q")
    If Cell.Value = "*.*" Then
        Cell.NumberFormat = "0.00"
    Else
        Cell.NumberFormat = "General"
    End If
Next

Does anyone know how I can do this? I would rather not have to add in any additional columns as the file is massive already.

Thanks for your help!

CodePudding user response：

I would use a sub like this - before applying the numberformat it checks for errors or text - and quits. By that you are sure that only numbers are handled.

Sub reformatNumber(c As Range)

If IsError(c.value) Then Exit Sub
If Not IsNumeric(c.Value2) Then Exit Sub

If Int(c.Value2) <> c.Value2 Then
    c.NumberFormat = "0.00"
Else
    c.NumberFormat = "0"
End If

End Sub

You can test it like this:

Sub test_reformat()
Dim c As Range
For Each c In Selection
    reformatNumber c
Next
End Sub

CodePudding user response：

Use conditional formatting. Example of it working:

Select range, format it as "0", add conditional formatting with custom formula "=A1<>round(A1,0)", with the number format "0.00"