I'm guessing, given the values produced by the drop-down list, that you
could replace your entire Select Case block with this single line of code...

Me.Range("G20:G30").NumberFormat = "[$" & Target.Value &"] #,##0.00"

Rick


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False

Select Case Target.Value
Case "USD"
Me.Range("G20:G30").NumberFormat = "[$USD] #,##0.00"

Case "INR"
Me.Range("G20:G30").NumberFormat = "[$INR] #,##0"

Case "CAD"
Me.Range("G20:G30").NumberFormat = "[$CAD] #,##0.00"

End Select
endit:
Application.EnableEvents = True
End Sub

This is event code. Right-click on the sheet tab and "View code".

Copy/paste the code into that sheet module.


Gord Dibben MS Excel MVP

On Thu, 13 Sep 2007 16:23:34 -0700, Randy wrote:

I wonder if anyone knows how to have a column in Excel 2003
automatically formatted to a particular currency when this particular
currency is selected from a drop down list in another cell?

i.e,;

Cell H14 has a drop down list to select USD, Canadian or Indonesian
Rupiah.

Cells G20 to G30 contain the Price column that I want to have
automatically formatted to either 2 decimal places for USD currency or
zero decimal places for the Indonesion Rupiah depending on what is
selected in H14.

Should I use a formula to start a macro or something like that.