1

I’m looking for a macro in excel that will help me, currency formatting a cell based on the value/text of another cell. I.E: Cell B28 is my number (567) that I want to currency formatting. Cell B27 is the currency I want to use, let’s say USD. What I want is that if B27 referencing USD, then B28 number will be converted into $567.

user3176630
  • 11
  • 1
  • 2
  • Do you want a one-time conversion, or do you want to enter something like `=myConverter(567, B27)` in cell B28, and have it display `$567`? How do you plan to indicate the currency? – Floris Jan 09 '14 at 08:42

2 Answers2

0

You can apply conditional formatting via Home -> Conditional Formatting -> New Rule

enter image description here

Select the relevant cell or cells, create a new rule, and in the formula check to apply the formatting only when B27 equals to "USD".

mockinterface
  • 14,452
  • 5
  • 28
  • 49
  • I suspect this may be hard to maintain as you change the worksheet, add new currencies, etc. Do you have a good solution for that? – Floris Jan 09 '14 at 08:55
  • You'll have to use VBA. See this questions if you need to create and maintain this programmatically: http://stackoverflow.com/questions/13661965/conditional-formatting-using-excel-vba-code – mockinterface Jan 09 '14 at 08:59
0

You could create the following function in a module in your workbook:

Function myConverter(v, r As Range)

 Select Case r.Value
 Case "USD"
   myConverter = Application.WorksheetFunction.Text(v, "$0")
   Exit Function
 Case "GBP"
   myConverter = Application.WorksheetFunction.Text(v, "£0")
   Exit Function
 ' add any other cases you want here...
 Case Else
 ' trap "unknown case": you could return "unknown format" or something else
   myConverter = v
 End Select

End Function

Now you can enter

=myConverter(567, A2)

where cell A2 contains USD, and get

$567

Obviously you can do anything you want with the Text format string...

Floris
  • 45,857
  • 6
  • 70
  • 122