Dynamic number formatting

[valenta79]

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).

 

[Tom Urtis]

Format values where this formula is true:

=$B$1=1=TRUE

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

Are you using version 2007? Sounds like it but sounds like valenta79 is not, unless he missed that tab. This functionality is not available in pre-2007 version.

 

[rconverse]

Are you using version 2007? Sounds like it but sounds like valenta79 is not, unless he missed that tab. This functionality is not available in pre-2007 version.

Yes, I'm using 2007. I didn't know that tab was missing from 2003. It's been a while since I've used 2003. My apologies to the OP if my suggestion isn't applicable.

 

[valenta79]

Rconverse,

Your solution is very close, it worked...almost. If my situation was as simple as #1 or #2 in the cell B1 this would have worked.

Now let me explain why I have #1 or #2 in that cell. I am using form control which is linked to that cell. So little control box have options to select Sales or Bookings. Whenever I select sales cell B1 (which is linked to form control) displays "1" and when i select bookings that cell displays "2".

If i am simply changing from 1 to 2 in cell B1 then your solution of conditional formatting is working. But it is NOT WORKING when I am switching back and forth in my form control. Have no idea why excel can not do it.

I am using 2007 Excel.

I can email a simple file and any of you guys can try to do this. It blows my mind why Excel is not capable of changing number format while using list box (form control).

 

[jiejenn]

What's your e-mail so I can send you the solution I created.

 

[rconverse]

What's your e-mail so I can send you the solution I created.

You don't want to share with the board?

 

[rconverse]

Got your PM. I know what you're talking about and it's frustrating.

I used this code to work around that problem. You may have to tweak the formats depending on what you're looking for. (I removed the conditional formatting.)

Sub ListBox1_Change()

If Sheets("Sheet1").Range("B1").Value = 1 Then

    Sheets("Sheet1").Range("D1:E1").NumberFormat = "0.0%"

Else

    Sheets("Sheet1").Range("D1:E1").NumberFormat = "0.0"

End If

End Sub

HTH,
Roger