Conditionally format a number format

[sdohertyccb]

Can I conditionally format a number format?
For example, I have a worksheet that has several numbers that update, and one of the fields is a format field, i.e. $, %.
If the format field is $ then I want to format the cell as a number, if the format field is a percentage, then format as a percentage.
I can see that I can format cells (highlight, underline, etc) based on a condition, but not the number format itself.
Anyone else run into this problem?

 

[Firefly2012]

You can't apply the full range of formatting options via Conditional FOrmatting I'm afraid. You could probably use event code if that is an option?

 

[sdohertyccb]

Thanks for your input. Any ideas about code I could use to accomplish this?

 

[Firefly2012]

Given this kind of set up in your sheet (can be easily modified to take account of other columns/ranges):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Format Field</td><td style=";">Value Field</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">$</td><td style="text-align: right;;">£100.00</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">%</td><td style="text-align: right;;">5.0%</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">%</td><td style="text-align: right;;">7.0%</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">$</td><td style="text-align: right;;">£100.00</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

Right-click on tab name in Excel (ie at the bottom of your sheet) and select View Code. Paste the following into the code window which will open:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cell As Range
Set rng = Intersect(Target, Range("A2:A5")) ' A2:A5 is the range that contains the format values eg % or $
'now check to see if any format field values changed:
If Not rng Is Nothing Then
    'now iterate thru all changes and apply to the value field (1 column over from format field in my example)
    For Each cell In rng
        If InStr(cell.Value, "%") > 0 Then
            cell.Offset(, 1).NumberFormat = "0.0%"  'amend number format to suit
        Else
            cell.Offset(, 1).NumberFormat = "$#,##0.00" 'amend number format to suit
        End If
    Next cell
End If
End Sub

Now, each time you change the value in A2:A5 (say change % to $) the corresponding cell in B2:B5 will change number format to that defined in the code.

 

[sdohertyccb]

FireFly, This is awesome! I have tried to figure this out for months. Works like a charm.
Thanks so much for your help. THere are so many brilliant and helpful people on this site, thanks for keeping us sane.

 

[Firefly2012]

You're welcome

 

[T. Valko]

Can I conditionally format a number format?
For example, I have a worksheet that has several numbers that update, and one of the fields is a format field, i.e. $, %.
If the format field is $ then I want to format the cell as a number, if the format field is a percentage, then format as a percentage.
I can see that I can format cells (highlight, underline, etc) based on a condition, but not the number format itself.
Anyone else run into this problem?

What version of Excel are you using?

If you're using Excel 2007 or later you can do this with conditional formatting.

 

[sdohertyccb]

Firefly,
If I type the format symbol in the format field, everything changes as advertised, but if I pull this value in through a formula, no changes occur. Is there a modification I can use to effect the format when the value is pulled in via a formula. I have modified your code slightly as follows:

Private Sub Worksheet_Change_2(ByVal Target As Range)
Dim rng As Range, cell As Range
Set rng = Intersect(Target, Range("A52:A57")) ' A52:A57 is the range that contains the format values eg % or $
If Not rng Is Nothing Then
    For Each cell In rng
        If InStr(cell.Value, "$") > 0 Then
            cell.Offset(, 10).NumberFormat = "$#,##0"
            cell.Offset(, 12).NumberFormat = "$#,##0"
            cell.Offset(, 14).NumberFormat = "$#,##0"
            cell.Offset(, 16).NumberFormat = "$#,##0"
            cell.Offset(, 18).NumberFormat = "$#,##0"
            cell.Offset(, 20).NumberFormat = "$#,##0"
            cell.Offset(, 22).NumberFormat = "$#,##0"
        ElseIf InStr(cell.Value, "#") > 0 Then
            cell.Offset(, 10).NumberFormat = "#,##0.00"
            cell.Offset(, 12).NumberFormat = "#,##0.00"
            cell.Offset(, 14).NumberFormat = "#,##0.00"
            cell.Offset(, 16).NumberFormat = "#,##0.00"
            cell.Offset(, 18).NumberFormat = "#,##0.00"
            cell.Offset(, 20).NumberFormat = "#,##0.00"
            cell.Offset(, 22).NumberFormat = "#,##0.00"
        Else
            cell.Offset(, 10).NumberFormat = "0.00%"
            cell.Offset(, 12).NumberFormat = "0.00%"
            cell.Offset(, 14).NumberFormat = "0.00%"
            cell.Offset(, 16).NumberFormat = "0.00%"
            cell.Offset(, 18).NumberFormat = "0.00%"
            cell.Offset(, 20).NumberFormat = "0.00%"
            cell.Offset(, 22).NumberFormat = "0.00%"
        End If
    Next cell
End If
End Sub

 

[Firefly2012]

You can use the Worksheet_Calculate event to monitor and respond to changes in formula cells (see code below).

Did you see Biff's query though? I wasn't aware that if using xl2007+ you can change the number format using Conditional Formatting.

'the following goes in the same module as the Worksheet_Change
 
Private Sub Worksheet_Calculate()
Dim rng As Range, cell As Range
Set rng = Range("A52:A57") ' A52:A57 is the range that contains the format values eg % or $
For Each cell In rng
        If InStr(cell.Value, "$") > 0 Then
            cell.Offset(, 10).NumberFormat = "$#,##0"
            cell.Offset(, 12).NumberFormat = "$#,##0"
            cell.Offset(, 14).NumberFormat = "$#,##0"
            cell.Offset(, 16).NumberFormat = "$#,##0"
            cell.Offset(, 18).NumberFormat = "$#,##0"
            cell.Offset(, 20).NumberFormat = "$#,##0"
            cell.Offset(, 22).NumberFormat = "$#,##0"
        ElseIf InStr(cell.Value, "#") > 0 Then
            cell.Offset(, 10).NumberFormat = "#,##0.00"
            cell.Offset(, 12).NumberFormat = "#,##0.00"
            cell.Offset(, 14).NumberFormat = "#,##0.00"
            cell.Offset(, 16).NumberFormat = "#,##0.00"
            cell.Offset(, 18).NumberFormat = "#,##0.00"
            cell.Offset(, 20).NumberFormat = "#,##0.00"
            cell.Offset(, 22).NumberFormat = "#,##0.00"
        Else
            cell.Offset(, 10).NumberFormat = "0.00%"
            cell.Offset(, 12).NumberFormat = "0.00%"
            cell.Offset(, 14).NumberFormat = "0.00%"
            cell.Offset(, 16).NumberFormat = "0.00%"
            cell.Offset(, 18).NumberFormat = "0.00%"
            cell.Offset(, 20).NumberFormat = "0.00%"
            cell.Offset(, 22).NumberFormat = "0.00%"
        End If
Next cell
End Sub

 

[sdohertyccb]

Great, that worked.
I am unfortunately stuck with 2003. This spreadsheet is large with many defined named ranges, and when I try to run it in 2010 it gags when it tries to calculate, even though I have elimiated all excess ranges..
Thanks again for your help.