In VBA how to select the right format for a range

[Guest]

Hi,
I'm trying to change format depending the data in a range.
In the cell D10 a "signe $%#", will tell vba to select the right format like
$ or % or #, etc.
The format need to change the range D1117

Exemple if D10 show: $ the range will be change to currency, if the cell D10
show % the range will change to % with 2 decimal.

I did something like this:

Sub FORMATCol()
Dim Col As Range
For Each Col In Range("D1170")
If Range("D10") = "$" Then
Col.NumberFormat = "$#,##0"
End If
Next
' Note: Section if not $ change for #
For Each Col In Range("D1170")
If Range("D10") = "#" Then
Col.NumberFormat = "#,##0"
End If
Next

End Sub

Problem is: I dont know how to tell the sub to select the right format IF
D10 =$ or #
I tried to add another IF (see the line after the ' ) in it, however it's
not working. Can you explain how to to this? Thank you

 

[Guest]

Sub FORMATCol()
Dim s as String
Select Case Range("D10").Text
Case "$"
s = "$ #,##0"
Case "%"
s= 0.00%"
Case "#"
s = "#,##0"
Case Else
s = "General"
End Select
Range("D1117").Numberformat = s
End Sub

You have shown both D17 and D70, so adjust the D17 to D70 if that is what it
is supposed to be.

 

[Bernie Deitrick]

Mouimet,

Your could use something like

Sub FORMATCol2()
Range("D1170").NumberFormat = Range("D10").Value & "#,##0.00"
End Sub

or, if you want to change the number of decimal places

Sub FORMATCol3()
If Range("D10").Value = "$" Then
Range("D1170").NumberFormat = "$#,##0"
End If

If Range("D10").Value = "#" Then
Range("D1170").NumberFormat = "#,##0.00"
End If

If Range("D10").Value = "%" Then
Range("D1170").NumberFormat = "%#,##0.0"
End If

End Sub

HTH,
Bernie
MS Excel MVP

 

[Guest]

Just curious, but I don't think I have ever seen the % in front like that

? activecell.Text
%23.14

Is that a common format at the Consumers Union? Elsewhere?

 

[Bernie Deitrick]

No! I just wasn't thinking clearly, and never ran the code....

Bernie

 

[Tom Ogilvy]

OK. Just curious - it was a good idea anyway.

 

[Guest]

HI,
Sorry if i'm sending this 3 weeks after your answer. I was a lot busy.
Thank you it worked well.
This is also very good for another template.
I have a small question:
I use a combo list to select the numbers. After I select the number I need I
need to click on a cell before the format change.
Do you have a way to make the change of the format at the same time I select
something in the combo list?
Thanks Again

 

[Bernie Deitrick]

You could use the change event: see example code below. Copy the code, right-click the sheet tab,
select "View Code" and paste the code into the window that appears. Of course, you will need to
adjust your range references....

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$D$10" Then Exit Sub
Range("D1170").NumberFormat = "0." & Application.WorksheetFunction.Rept("0", Target.Value)
End Sub