Change currency formatting on value of a cellClosed

Question

Hello,
I have a conditional formatting issue in Excel 2007.

I have a drop down list which shows 4 countries; UK, DE, FR and NL

I would like to change the format of a number of cells to £ or € dependent on what the dropdown box has chosen.

I have a number of vlookups, so it pulls the right amount, and the right SKU, but I cannot get it to change the formatting correctly.  I have even tried to simplify it to say, if UK use £ and if <> UK then use €, but that doesn't change either.  If I change it, so it colours red for UK, and clear for not UK, that works.

Can anyone help please?
Thanks, 17ND

RWomanizer · Accepted Answer

Hi 17ND, 

suppose your coutries in coulmn B and number in cell D then use conditional formating as 

in Formula use =$B1="UK"  in format select curreny £ and in applies to give range to column D,
and add new rule as
in Formula use =$B1="DE"  in format select curreny € and in applies to give range to column D and so on.

Hi thumic, 

remove one $ from your formula
=$A$1="SEK"
write it as 
=$A1="SEK",

Regards,
Rahul

thumic · Answer

Maybe we can work together on this... I'm in the same situation as you are. I've created a list of currencies using "Name Manger" and put them as a drop down selection in A1.  Below that I got different values to be entered or auto calculated. I now want these values to "adopt" the currency chosen from the drop down above. I've used "Conditional Formatting" - "Manage Rules" and added, for each currency, the following rule /=$A$1="SEK"/ (without the / ) then in format I've set the currency to "SEK" without commas - hit OK. Click new rule and continue /=OR$A$1="Dollar"/ then in format I've set the currency to "$US" without commas and so ... for each currency I've got in there, I've created a rule in conditional formatting. Once you're done with all of them hit Apply.   It works abosultely great when changing 1,2 times but if you change more often, it gets stuck on (in my case) the last currency in the list used in drop down, and when you select one that was earlier on the list it won't change back automatically. I've tried "Data" - "Refresh All" no luck... I've tried checking and unchecking "Stop if true" in the conditional formatting window for one, some and all of the rules - no luck.... Maybe my little workaround helps you in getting to a working solution and by that we've helped each other out... Best regards and good luck, Michael

Change currency formatting on value of a cell

2 replies

Subscribe To Our Newsletter!