Okay, maybe I missed the point ...
Try this UDF
In a Standard Module
Function GetFormat(rng As Range)
GetFormat = Mid(rng.NumberFormat, 3, 3)
End Function
Enter in B2, Drag/Fill Down as required.
Formula:
=IF(A2="","",GetFormat(A2))
In the sheet module, this will format the result in D3
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "D2" Then
Select Case Target
Case "AUD", "USD", "EUR"
Range(Target.Address).Offset(1, 0).NumberFormat = "[$" & Target & "] #,##0.00"
Case Else
Range(Target.Address).Offset(1, 0).NumberFormat = "General"
End Select
End If
End Sub
Select from the drop-down in D2
Bookmarks