Code needed to replace all Custom formatted cells with currency formatted cells

I have designed a spreadsheet that people in other countries will be using. All the currency cells are formatted as currency, so for me the $ symbol shows. When I go into my control panel and change the default currency to Euros or something, then all the cells formatted as currency change to the Euro symbol, which is perfect. However, when I close out of the workbook and then reopen it, it no longer shows the Euro symbol. It shows the American dollar because all of the cells formatted as currency got switched to custom ("$"#,##0.00), so it doesn't read the computer's default currency setting and instead forces the $ symbol since it is a custom setting.

When I change my control panel back to the American dollar, the cells are once more formatted as currency, not that custom setting.

I had a gal in another country test it, and same thing. She can't get the pound symbol at all. Since her computer settings are already configured to the pound symbol, when she opens the workbook for the first time, all of the currency cells are custom cells forcing the $.

This is a problem since my workbook will be used from people in a variety of countries. (No need to do any conversions as the user will be entering in the amount. I just need the symbol to change).

I went in and did a find and replace.
Find all cells formatted with "$"#,##0.00; [Red]"$"#,##,0.00 and replace with Currency formatting
Find all cells formatted with "$"#,##0.00 and replace with Currency formatting.

This then changed all the custom cells back to currency cells, and it would read the computer's currency setting ( I had my set at Euros to test). Then I saved and closed and upon reopen it was still there. Perfect.

So now I just need a code that when a user opens the workbook for the first time, they can click on change currency to mine, and it will automatically run this find and replace for them.

I attempted to create the macro by simply recording my find and replace steps. However, it won't execute the recorded VBA. It just gives an object-defined error.

Please Login or Register  to view this content.

Can anyone think of a code that would find all custom cells and replace them with currency?