Do you want to convert the British Pound to a USD$, or just reformat the cell (another words, change British Pound 1.00 to $1.00)?
Just change British Pound 1.00 to $1.00.
What happens is the data is a BP but my template might be formatted as a USD.
I want to be able to switch between US and BP on the same template without having to manually change the formatt
Here is a simple macro that you can use to switch back and forth:
Sub Macro1()
Columns("A:A").Select
If Selection.NumberFormat = "$#,##0.00" = True Then
Selection.NumberFormat = "[$£-809]#,##0.00"
Else: Selection.NumberFormat = "$#,##0.00"
End If
Range("a1").Select
End Sub
This is assuming that your data is in Column A. It selects the entire column and changes the format to either Pounds or $, which ever one it is not. If you only want to select a range, you can replace "Columns("A:A").Select" with "Range("a1:a10").Select", A1:A10 being the range that you want to change.
Copy & Paste the macros below to your Macro module (Macro-Macros, Edit or Create).
If you then set the Macro - Option to a Hot-key you can convert the format back and forth.
Note: This will only reformat the active cell, it does not convert the value. With this code; 1 pond will = $1.00 it will not convert 1 pond to $2.31 lets say!
If you set the Hot-key codes as below then if you have a value in the active cell and hit "Ctrl-p" no-matter what format the value was in it will be in ponds to two places. If you hit Ctrl-d then no-matter what format the value is in it will now be in $0.00 format. Hope this helps. JSW
Sub Pound()
'Macro-Options assign Hot-key =p
ActiveCell.NumberFormat = "[$£-809]#,##0.00"
End Sub
Sub Dollar()
'Macro-Options assign Hot-key =d
ActiveCell.NumberFormat = "[$$-409]#,##0.00"
End Sub
Thanks Scott it works great No Msg...