Excel Custom number format

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.

Please help
GRChandrashekarAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
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

Open in new window


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
 
Rgonzo1971Commented:
HI,

What should be shown if negative numbers?

Regards
0
 
GRChandrashekarAuthor Commented:
it should be like (1,57,300)
now it shows -157,300
but positive shows correct 1,57,300
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
barry houdiniCommented:
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
 
GRChandrashekarAuthor Commented:
can we re-write the custom format and accommodate this ?
0
 
barry houdiniCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.