Excel VBA Question - Changing Format Cell To Particular Currency

[iggyntangs]

Hi all,

I've been trying to do IF statements and case statements to achieve this and nothing has been remotely successful.

Basically I have a 5 tab workbook that has different named ranges within each sheet that are meant to be a currency format.

However based on the value of a cell in the 1st tab (e.g. it could be CAD, USD, NOK, etc.) the currency format on those range of cells should change.

I.e. If the cell value in tab 1 is equal to CAD then all the named ranges in tabs 2-5 should be "$", if USD still "$", if NOK "kr " etc.

Any help would be greatly appreciated!

[Old Yeller]

I haven't given this much thought, but could you not just have a hidden sheet with a lookup table with the currency in column A and the corresponding currency symbol in column B and then do a vlookup based on that?

[nfotiu]

Conditional formatting will do exactly what you want.

The format formula would look something like =(B1="NOK") and then you can click on the Format button and choose the currency format you want.

[Flames89]

Yep, you can do the above Conditional by making your own rule on the cell, and selecting a formula to fill. Do a new rule for each currency.


Or just do a nested IF with the TEXT(reference, format) formula.
Like,
=IF(C3="CAD", TEXT(B3, "$0,000.00"), IF(C3="EUR", TEXT(B3, "€ 0,000.00", etc....
You will need to have to prepare the funky symbol currency text formats before typing the full formula so you can just copy/paste in the "€ 0,000.00", for example