Cell Format variable depending on another cell value

[elobelix]

Hi, Ive got a question for you guys:

I want a macro that, based on a cell text content, defines the format of another cell.

In other words: I have a colum (A) named that contains currency data as text, and another colum (B) with the monetary value ($6000).

If colum A is "dolars" I want column B to have the money format for USD 6.000
If colum A is contains any other text value (it can say "pesos", "euros", etc etc) I want column B to have the money format for $ 6.000

How can you do this??

thanks a lot!

[Andrew-R]

OK, here's a function and a sub-routine for you:

Function AutoCurrency(CurrVal, CurrType)
Dim Symbol

Select Case LCase(Trim(CurrType))
Case "dollars"
  Symbol = "$"
Case "pounds"
  Symbol = "£"
Case "yen"
  Symbol = "Y"
Case "euros"
  Symbol = "E"
Case Else
  Symbol = "?"
End Select

If IsNumeric(CurrVal) Then
  AutoCurrency = Format(CurrVal, Symbol & "#,##00.00")
Else
  AutoCurrency = "#N/A"
End If

End Function

Sub ApplyCurrencyToRange()
Dim CellLoop As Range

For Each CellLoop In Selection.SpecialCells(xlCellTypeConstants)
  CellLoop.Value = AutoCurrency(CellLoop.Value, CellLoop.Offset(0, -1).Value)
Next CellLoop

End Sub

The function (which can be used as a worksheet function) takes a numeric value and a string containing the currency type and applies the correct symbol to it and formats it as a currency value. You can add as many Case "xxx" statements as you want, setting the symbol for each one.

Note, however, that it will be sensitive to say, spelling "dollars" as "dolars".

The subroutine applies this function to all currently selected cells, using the cell immediately to the left of each one to get the currency type. See how that works for you.

[elobelix]

Thanks! This works just fine