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("D11:D17").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.
--
Regards,
Tom Ogilvy




"Mouimet" wrote:

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 D11:D17

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("D11:D70")
If Range("D10") = "$" Then
Col.NumberFormat = "$#,##0"
End If
Next
' Note: Section if not $ change for #
For Each Col In Range("D11:D70")
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