One way:
Use a worksheet change event macro. Put this in your worksheet code
module (right-click on the worksheet tab and choose View Code):
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "A1" Then
Application.EnableEvents = False
.Value = Application.Round(.Value * _
Evaluate(ThisWorkbook.Names("Conv_Rate").Value), 2)
.NumberFormat = "$#,##0.00"
Application.EnableEvents = True
End If
End With
End Sub
This assumes you have a name "Conv_Rate" (Insert/Name/Define...) with
the conversion rate. If you have the conversion rate in a cell, use
.Value = Application.Round(.Value * Range("B1").Value, 2)
Note that, with XL00/02/03 you can use VBA's Round() function, which
gives slightly different results - i.e., rounding a 5 in the last digit
to the nearest even number, instead of ROUND()'s rounding away from zero.
