millions and thousand custom number formatting VBA

cip888

New Member
Joined
Jul 18, 2014
Messages
11
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

 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,037
Office Version
  1. 365
Platform
  1. Windows
Try

Code:
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

New Member
Joined
Jul 18, 2014
Messages
11
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

Well-known Member
Joined
Jan 11, 2012
Messages
4,037
Office Version
  1. 365
Platform
  1. Windows
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

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,924
Messages
5,525,657
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top