Cell format

Colleen45

Active Member
Joined
Jul 22, 2007
Messages
495
Is it possible to change the format of a cell from currency to percentage based on another cells value (1 or 2)?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
You can't change the number format (without VBA), but you can use the TEXT function to display the value differently.
 
Upvote 0
Thanks Andrew, I thought as much, so this begs the question, what would the VBA code be?
 
Upvote 0
You could consider using conditional formating to switch between the two formats eg:

CF applied to B1 of:

=$A$1=2

Then apply Percentage number format.

You then need to ensure that B1 has Currency number format applied to it (ie its normal number format).

Changing A1 to a 2 will then end up applying currency format to the cell.

This is by no means a perfect solution on my Excel 2007 - the change to % is automatic, but for some reason it does not revert automatically to CCY if A1 is amended to something other than 2 (I have to hit F9).
 
Upvote 0
I'm thinking of using "If then else" in VBA, IF cell AU5 = 1, then format cells E13:W13 in Currency, if cell AU5 = 2, format in cells E13:W13 percentage, but I don't know how to put it all together.
This is the line for percentage, Selection.NumberFormat = "0.00%" and this is the line for Currency, Selection.NumberFormat = "$ #,##0.00", I'm somewhat lost.
 
Upvote 0
Assuming AU5 contains a hardcoded constant (rather than a formula returning a 2 or a 1) then right-click on your tab name at the bottom of Excel, choose View Code, and paste the following into the code module which will open:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$AU$5" Then Exit Sub
Select Case Target.Value
    Case 1
        Range("E13:W13").NumberFormat = "$#,##0.00"
    Case 2
        Range("E13:W13").NumberFormat = "0.00%"
    Case Else
        'currently nothing here - so values other than 1 or 2 do not change the existing format
End Select
End Sub
 
Upvote 0
Battle as I may, I can't get this code to run, AU5 is not fixed, it changes to 1 or 2, depending on the control button selected!
 
Upvote 0
There is no code, When you select control button 1, a value of 1 is returned to AU5, when control button 2 is selected, it returns a value of 2, based on either of these choices, I would like the format changed to currency, or percentage, in the range mentioned above
 
Upvote 0
Are you sure there's no code attached? I may be missing something (hopefully Andrew can jump back in here if so) but I don't see a way to affect a cell value using a Command Button without involving code.

Is it from the Forms menu or Controls Toolbox (in which case it is ActiveX)? With Design Mode on (choose this on the Developer ribbon) when you select the button do you see a formula in the formula bar along the lines of:

=EMBED("Forms.CommandButton.1","")

Otherwise, are you sure it is actually a command button and not some other control? With DesignMode on, when you right-click the border of the button do you get an option to look at Properties?
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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