2003 Conditional Number Format Help

[F00]

This site is great and I appreciate all the help everyone offers. Need help with something here.

I'm currently using version 2003 and need help with Number Formatting based on a drop-down choice. I'm not VBA savvy at all so I need some help.

Here is the situation:

Cell F2 contains a drop down with two choices: "$ Dollar" and "% Percent"

If the user chooses "$ Dollar" I need a currency (two decimals) format for the following cell ranges:

H2:H82, J2:J82, L2:L82, N2:N82, P2:P82, R2:R82, T2:T82, V2:V82, X2:X82, Z2:Z82, AB2:AB82, AD2:AD82, AF2:AF82, AH2:AH82

If the user chooses "% Percent" I need a percentage format (one decimal) for the following cell ranges:

H2:H82, J2:J82, L2:L82, N2:N82, P2:P82, R2:R82, T2:T82, V2:V82, X2:X82, Z2:Z82, AB2:AB82, AD2:AD82, AF2:AF82, AH2:AH82

One last detail is that this is a form that starts blank and I'd like the user to be able to make the choice in the drop-down and then type in the number in the cell ranges listed above. Thanks!

 

[Yard]

Try something like this to get you started:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Fmt As String

If Not Intersect(Target, Range("F2")) Is Nothing Then

    Select Case Target
    Case "Dollar"
        Fmt = "$0.00"
    Case "Percent"
        Fmt = "0.0%"
    Case Else
        Fmt = "General"
    End Select
    
    Range("H2:H12,J2:J12").NumberFormat = Fmt
    
End If

End Sub

To make this work, right-clcik on your sheet tab, click View Code, paste this code into the code window. Press Alt+Q to return to Excel and it should work when you change the value in F2.

 

[F00]

That worked! I appreciate the help.