Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Dynamic number formatting

This is a discussion on Dynamic number formatting within the Excel Questions forums, part of the Question Forums category; I have an issue with number formatting. Conditional formatting does not seem to handle this problem. I want my cells ...

  1. #1
    Board Regular
    Join Date
    Dec 2009
    Posts
    64

    Default Dynamic number formatting

    I have an issue with number formatting. Conditional formatting does not seem to handle this problem. I want my cells to display $ sign whenever certain condition is met or to display regular number formatting when that condition is not met.

    Here is a simple example:

    I have data in cells A1:A10, cell B1 controls formatting for that data. If I have number "1" in cell B1 then I want my numbers in cells A1:A10 to have $ signs in front of them (currency format), and if i have "2" in cell B1 then i want numbers to be in regular number format.

    Maybe there is a macro i can use here? I tried conditional formatting and it is not working for number formats (I can change cell colors, bold text etc with conditional formatting but it is not working for number formats).

  2. #2
    New Member
    Join Date
    Jun 2010
    Location
    Texas
    Posts
    20

    Default Re: Dynamic number formatting

    Hello there, give this a try:

    Code:
    Sub ChangeFormat()
    '
    ' ChangeFormat Macro
    ' Will change number formatting based on value of cell B1
    '
    
    '
        If Range("B1") = 1 Then
        Range("A1:A10").Select
        Selection.Style = "Currency"
        End If
        
        If Range("B1") = 2 Then
        Range("A1:A10").Select
        Selection.Style = "Comma"
        Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        End If
        
        Range("A1").Select
        
    End Sub

  3. #3
    Board Regular
    Join Date
    Dec 2009
    Posts
    64

    Default Re: Dynamic number formatting

    It did not work.....

  4. #4
    New Member
    Join Date
    Jun 2010
    Location
    Texas
    Posts
    20

    Default Re: Dynamic number formatting

    My apologies, I am assuming you want it to trigger automatically. I will update and repost

  5. #5
    Board Regular
    Join Date
    Nov 2007
    Location
    Chicago, IL
    Posts
    1,161

    Default Re: Dynamic number formatting

    I did not need a macro. I just used conditional fomatting with this formula.

    =$B$1=1=TRUE

    HTH,
    Roger

  6. #6
    New Member
    Join Date
    Jun 2010
    Location
    Texas
    Posts
    20

    Default Re: Dynamic number formatting

    Add the following macro then save, close and reopen your file.

    Code:
    Sub auto_open()
    
       ' Run the macro ChangeFormat any time a entry is made in
       ' cell B1 in Sheet1.
       ThisWorkbook.Worksheets("Sheet1").OnEntry = "ChangeFormat"
    
    End Sub

  7. #7
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    10,984

    Default Re: Dynamic number formatting

    Quote Originally Posted by rconverse View Post
    I did not need a macro. I just used conditional fomatting with this formula.

    =$B$1=1=TRUE

    Roger
    and how did that change the number formats from currency to non currency?

  8. #8
    New Member
    Join Date
    Jun 2010
    Location
    Texas
    Posts
    20

    Default Re: Dynamic number formatting

    Quote Originally Posted by rconverse View Post
    I did not need a macro. I just used conditional fomatting with this formula.

    =$B$1=1=TRUE

    HTH,
    Roger
    Very nice.

  9. #9
    Board Regular
    Join Date
    Nov 2007
    Location
    Chicago, IL
    Posts
    1,161

    Default Re: Dynamic number formatting

    Quote Originally Posted by Tom Urtis View Post
    and how did that change the number formats from currency to non currency?
    Format values where this formula is true:

    =$B$1=1=TRUE

    Then click on Format, select the number tab, and click on currency.

  10. #10
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    10,984

    Default Re: Dynamic number formatting

    Quote Originally Posted by valenta79 View Post
    I have data in cells A1:A10, cell B1 controls formatting for that data. If I have number "1" in cell B1 then I want my numbers in cells A1:A10 to have $ signs in front of them (currency format), and if i have "2" in cell B1 then i want numbers to be in regular number format.
    What is the reason why you'd put a 1 or a 2 in cell B1?

    Maybe VBA is not necessary depending on why the 1 or 2 is there.

    Example, if you want to format numbers greater than 100 with a preceding dollar sign as currency-looking, and numbers from zero to 99.9999 as numbers without a dollar sign and 2 decimals, just for demo purposes I suggest these 2 scenarios, then this custom format in A1:A10 would do that:

    [>=100]0.00;[>0]$#;0

    I have a hunch your situation is more complex, but just in case the format depends on the number's size, this is one way to go about it sans code.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com