millions and thousand custom number formatting VBA

[cip888]

Hi everyone
I would like to display:
1,200,000 as $1.2M & 120,000 as $120K as well as -1,200,000 as -$1.2M & -120,000 as -$120k for a range of cell

In order to do that I know that for the positive number the format is :

[>=1000000] $#,##0.0,,"M";[>0] $#,##0.0,"K";General

and for negative:

[<= -1000000]-$#,##0.0,,"M";[<0]-$#,##0.0,"K";General

however I need a macro that apply these two different set of format depending on whether the number is positive or negative, also the number that I need to be formatted come from a formula and are not typed in

any help would be greatly appreciated

 

[Momentman]

Try

Sub caseformat()
    Dim cell As Range
    For Each cell In Selection
        Select Case cell.Value
            Case Is < 0
                cell.NumberFormat = "[<= -1000000]-$#,##0.0,,""M"";[<0]-$#,##0.0,""K"";General"
            Case Is > 0
                cell.NumberFormat = "[>=1000000] $#,##0.0,,""M"";[>0] $#,##0.0,""K"";General"
            Case Else
                'whatever you want
        End Select
    Next cell
End Sub

 

[cip888]

Hi Momentman

Thank you for your reply the code works, however I have probably should have mentioned that the values for which I need this formats to apply are vlookup dependent.

Basically the vlookup is looking up values in a table that will change from time to time based on a combo box selection.

The problem is that everytime I select a different value in the combo box, the format does not change for some of the values in the cells I previously selected (and for which the macro is supposed to apply)

Is there a way to overcome this issue ?

Thanks again for your help !

 

[Momentman]

For which range of cells is the macro supposed to apply?

Your question suggests that we might need to use a worksheet_calculate event

I have assumed that the range that requires the formatting is N2:N7, change in the code as necessary

Right click the sheet, view code and paste this code in there

Private Sub Worksheet_Calculate()
    Dim rng As Range, cell As Range
    Set rng = Range("N2:N7")
    For Each cell In rng
        Select Case cell.Value
            Case Is < 0
                cell.NumberFormat = "[<= -1000000]-$#,##0.0,,""M"";[<0]-$#,##0.0,""K"";General"
            Case Is > 0
                cell.NumberFormat = "[>=1000000] $#,##0.0,,""M"";[>0] $#,##0.0,""K"";General"
            Case Else
                'whatever you want
        End Select
    Next cell
End Sub

 

[cip888]

Yep this one works great !! thank you so much for your help