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("D11:D70").NumberFormat = "0." & Application.WorksheetFunction.Rept("0", Target.Value)
End Sub


"Mouimet" wrote in message
...
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

"Tom Ogilvy" wrote:

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?

--
Regards,
Tom Ogilvy



"Bernie Deitrick" wrote:

Mouimet,

Your could use something like

Sub FORMATCol2()
Range("D11:D70").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("D11:D70").NumberFormat = "$#,##0"
End If

If Range("D10").Value = "#" Then
Range("D11:D70").NumberFormat = "#,##0.00"
End If

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

End Sub

HTH,
Bernie
MS Excel MVP


"Mouimet" wrote in message
...
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