Dynamic number formatting

valenta79

Board Regular
Joined
Dec 31, 2009
Messages
75
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).
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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
 
Upvote 0
I did not need a macro. I just used conditional fomatting with this formula.

=$B$1=1=TRUE

HTH,
Roger
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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
Back
Top