Solved

# Excel Custom number format

Posted on 2013-06-04
579 Views
I have the following custom format in excel.

[>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0

But this does not show -ve values in brackets and formatting  if value is -ve is not correct. but if positive it works correctly.

0
Question by:GRChandrashekar

LVL 48

Expert Comment

HI,

What should be shown if negative numbers?

Regards
0

Author Comment

it should be like (1,57,300)
now it shows -157,300
but positive shows correct 1,57,300
0

LVL 50

Expert Comment

I'm not sure you can add another format to your existing custom format - you can use conditional formatting to get the correct format for negative numbers, are you interested in doing that?

regards, barry
0

Author Comment

can we re-write the custom format and accommodate this ?
0

LVL 50

Expert Comment

No I don't think you can because when you start using >100000 etc. there's a limit to how many you can use (and I think you've reached it), what should be the format for <100000 but positive (is that possible)? Are all negative numbers formatted the same way or do you still want the same distinction as with positive?

regards, barry
0

LVL 92

Accepted Solution

Patrick Matthews earned 500 total points
You could try this.  Assuming your values are in Column A...

1) Put this code in the sheet module for the sheet you need "watched"

``````Private Sub Worksheet_Calculate()

Dim LastR As Long
Dim Counter As Long

Application.EnableEvents = False

With Me
LastR = .Cells(.Rows.Count, "a").End(xlUp).Row
For Counter = 1 To LastR
If .Cells(Counter, "a") >= 0 Then
cel.NumberFormat = "[>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0"
Else
cel.NumberFormat = "[<=-10000000](##\,##\,##\,##0);[<=-100000](##\,##\,##0);##,##0"
End If
Next
End With

Application.EnableEvents = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Updated As Range
Dim cel As Range

Set Updated = Intersect(Me.Range("a:a"), Target)
If Not Updated Is Nothing Then
Application.EnableEvents = False
For Each cel In Updated.Cells
If cel >= 0 Then
cel.NumberFormat = "[>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0"
Else
cel.NumberFormat = "[<=-10000000](##\,##\,##\,##0);[<=-100000](##\,##\,##0);##,##0"
End If
Next
Application.EnableEvents = True
End If

End Sub
``````

2) Now, if you manually change cell values in column A, or the worksheet recalculates, the different event subs will apply the custom number format on a case by case basis
0

## Featured Post

### Suggested Solutions

MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.