Search code examples
excelvbafunctionnumber-formatting

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).

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.

    -- UPDATE--

    To have more than two conditions you will have to use two conditional formatting rules:

    1. =AND(ABS(A1)>0,ABS(A1)<1) --> 0.00
    2. =OR(A1=0,ABS(A1)>=1) --> 0

    ABS is used to have the same logic for negative numbers.