You could use a Worksheet_Change() event. The one below will take a text string in A1 and convert the format in B1 to whatever is set in A1 (There is no Case Else statement, so currently it must be one in the list, this can be expanded though)
To place this, right click on the relevant sheet tab and select 'View Code', copy in here and then close the VBE and save.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
Select Case Target.Value
Case Is = "AUD"
Target.Offset(0, 1).NumberFormat = "_-[$$-C09]* #,##0.00_-;-[$$-C09]* #,##0.00_-;_-[$$-C09]* ""-""??_-;_-@_-"
Case Is = "USD"
Target.Offset(0, 1).NumberFormat = "_-[$$-409]* #,##0.00_ ;_-[$$-409]* -#,##0.00 ;_-[$$-409]* ""-""??_ ;_-@_ "
Case Is = "YEN"
Target.Offset(0, 1).NumberFormat = "_-[$¥-411]* #,##0.00_-;-[$¥-411]* #,##0.00_-;_-[$¥-411]* ""-""??_-;_-@_-"
Case Is = "EUR"
Target.Offset(0, 1).NumberFormat = "_-[$€-2] * #,##0.00_-;-[$€-2] * #,##0.00_-;_-[$€-2] * ""-""??_-;_-@_-"
Case Is = "GBP"
Target.Offset(0, 1).NumberFormat = "_-[$£-809]* #,##0.00_-;-[$£-809]* #,##0.00_-;_-[$£-809]* ""-""??_-;_-@_-"
End Select
End If
End Sub
Regards
Nick Hodge
Microsoft MVP, Excel
Southampton, UK