Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Number formatting based on cell value using vba function

I need to create a VBA as a Function, not a Sub, in Excel to format a range of cells based on the cells value. I call the function elsewhere in macro.

If the cell value is greater than or equal to zero, then display whole number format without decimals (100 or 0).

If the value is less than zero, then put in number format with 2 decimal places (0.05).

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

This is what I have, but I’m stuck (sorry, new to this):

Function NumberFormats()
    Call the_lastRow(wsX, colA)
    wsX.Range(colP & rowStart, colX & lastRow).NumberFormat = "General" '"#,##0.00"
End Function

>Solution :

You can use this number format:
0;-0.00;0

First part defines the format for positive value, second part for negative value, third part for zero.

If you eg. want two digits for values < 1 then use this number format [>1]0;0.00

Btw: there is no reason to have a function only because you call it from within another macro.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading