If you set up a 'Style' for 'changingCurency' you can set all cells to this format which need to change.
Then when you want to change the currency format it can be done in modify styles or throught VBA.

If you step through the example code in the attached workbook it should demostrate what can be done:

    Sub test()


    ActiveWorkbook.Styles("CurrencyChanges").NumberFormat = "£#,##0.00"
    
    ActiveWorkbook.Styles("CurrencyChanges").NumberFormat = "[$$-409]#,##0.00"

    ActiveWorkbook.Styles("CurrencyChanges").NumberFormat = "€#,##0.00"

    End Sub

Select allOpen in new window

U--Example.xlsm

I have a method that works very well allowing the user to choose a sign and then changes all the currency cells. Unfortunately it's at home but I'll post an xample this evening if it will help

Steve and Roy,

Thanks for responding.  Roy, I'll take a look at your approach when you send it tonight.  In the meantime, Steve, I'll review your links/comments.

Thank you,

B.

I'll knock up an example, it's actually part of a project of mine.

Perfect! Thank you.  This should be interesting.........

B.

Steve,

I tested your approach.... interesting use of "Styles"....... How do you automate that so you choose a currency and it populates all Currency cells in a WB?  

B.

Roy and Steve,

Thank you both for jumping in here.  I really like the easy conditional formatting idea from Roy because it seems simple and I can have a drop down for the Currency selection and avoid Macros and some complexity.  I think you would agree Steve, that this might be an easy way to accomplish the objective.  However, to Steve's point, if this is in a VB Module, then all cells formatted for "currency" would automatically change and I would avoid the Pasting formatting I'll need to do.  Is that right?

So here is where I am.  I've tried to work Roy's conditional formatting idea and I'm missing something in his directions.  I've attached my current "attempt".  Can you guys look at this and see what I'm doing wrong?  I've created a List in E1, and tried to set up 3 Conditional Formatted styles.  Copied each to the Test Cells in 1 and 2.  And I'm missing something basic.

Thank you, again,

B.
Currency-Formatting.xlsm

One downfall with using a macro is that the user must be made aware that macros need to be enabled, This is not always easy.

Here;s your workbook set up, sorry my example didn't upload.
Currency-Formatting.xlsm

I would tend to avoid Macro in a situation like this.
Either allowing the user to change the currency with styles or with conditional formatting.
Styles are OK if there are users of many different currencies (or you cannot predict the format requirements of the user).
Conditional formatting is good if thare are not too many known currencies (max 5 imo) as users find it simpler.
All down to preferences and what is being done.

Roy, this looks great!  And Steve, thanks for the validation!

Roy, what did you do to my WS to make it work?  I just need to know as I now transfer the knowledge to my production WB.

Thank you both.

B.

I added the correct named ranges - see Formulas Tab -> Name manager

Then added the Conditional Formatting formulas.

I'll do a more comprehensive instruction when I gat time this weekend, but post back with specific questions

I use this in a large accounts workbook with no problems.

Roy and Steve,

For embedded graphics within the WB, can I "link data to source" select and will it incorporate the new selection of currency in the graphic?

B.

I'm not sure what you mean. Why not try what you want to d on the example workbook

Doing that now....thanks!

Roy and Steve,

This Conditional Formatting solution works great and is simple and elegant.  Thank you both for helping me out with this.  The last question has to do with graphics that are embedded in the Workbook.  The currency for those graphics typically are changed by accessing the formatting in the graphic and then changing currency.  What I was wondering is, there is an option to "link to source" the data in particular charts and graphs.  If I do that, will the formatting automatically change?

I know this question is above and beyond the question you have already answered so I'm going to close out the question and hopefully, if you already know the answer, you may simply post an additional comment.

Great work again.... and thanks!

B.

Can you attach a example of what ou are using with the graphics