change currency format based on another cell format

[jimthomas22]

Hi
I am trying to change the currency format of one cell based on the currency format of another cell. So if cell A1 is £5.00 and cell B1 is £10.00 when the format of the master cell (A1) is change to $ then B1 will automatically change to $10.00.

Can anyone help?

Thanks Jim

 

[villy]

Try...

Sub CHangecurr()
If Range("A1").NumberFormat = "$#,##0.00" Then
    Range("B1").NumberFormat = "$#,##0.00"
Else
    With WorksheetFunction
    Select Case Mid(Range("A1").NumberFormat, 3, 3)
        Case "AED"
            Range("B1").NumberFormat = "[$AED] #,##0.00"
        Case "CAD"
            Range("B1").NumberFormat = "[$CAD] #,##0.00"
        Case "PhP"
            Range("B1").NumberFormat = "[$PhP] #,##0.00"
    End Select
    End With
End If
End Sub

 

[jimthomas22]

Thanks for the suggestion -
- firstly i hoped to use a formula instead of a macro so it happens auromatically, although, I guess, I can use some trigger on a change to A1
- secondly this macro does not seem to change the format of cell B1 - i added breaks and it always goes down the Else branch but still does not change the format of B1

Also I will not know what currency format will be applied to A1 - I just want that format applied automatically to B1.

I am using Excel 2007 but doubt if that is the cause?

Thanks