Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 647
  • Last Modified:

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
0
GRChandrashekar
Asked:
GRChandrashekar
1 Solution
 
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
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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
 
Patrick MatthewsCommented:
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now