Change Currency Format of Cell based on another Cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to dynamically change the currency format of a cell based on
the value of another cell dynamically.

For isntance cell A1 holds the text value "HKD", i want to change the
currency format of cell A2 to HKD. If the value in A1 is "SGD" then the
currency format of A2 will be SGD?

Is this possible?

Thanks.
Simon
 
Simon;

Create a column of text values for your currency. Then create a column of
character values for the symbol of that corresponding currency. Then create a
column of factorials based on a set currency. Do an If(lookup,Lookup,"")
statement that selects the value of the factorial based on the text value
that you assign in a predefined location.

God Bless

Frank Pytel
 
Simon

You could do this using sheet event code.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False

Select Case Target.Value
Case "HKD"
Target.Offset(1, 0).NumberFormat = "[$HKD] #,##0.00"

Case "SGD"
Target.Offset(1, 0).NumberFormat = "[$SGD] #,##0.00"

End Select
endit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.


Gord Dibben MS Excel MVP
 
Back
Top