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

 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,142
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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,142
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,981
Members
430,100
Latest member
namhnz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top