I am not quite sure what you want. Do you want to set up a spreadsheet which
will format currency  as Rands if it is used in South Africa, Yen if it is used
in Japan and Bahts if it is used in Thailand

or

Do you want on one spreadsheet to have some Rands, Some Yen, Some Bahts etc
and each to be formatted according to the country it relates to?

I want to set up a spreadsheet which will format currency  as Rands
if it is used in South Africa, Yen if it is used in Japan and Bahts
if it is used in Thailand.

1000'th sperator in few countries are not "," and i have to take care
of this also. display of currency symbol like '$' or 'euro symbol' is
not very necessary.

This should happen automatically. If you use the Currency format, it will
pick up the appropriate one from the Regional Settings in Windows.

What I have understood, that currency  should be fromatted on the basis of the
country. meaning If the same sheet being open in India the currency would be
formatted to RS, If it is US it would be $ like this.
If this is what I understood, we need to write a VBA code using this Lib
GetLocaleInfo Lib "kernel32", which will return you the country
GetCountryName = GetUserLocaleInfo(LCID, LOCALE_SISO3166CTRYNAME)
Now using the SELECT...CASE you can change the global setting of the currency
irrespective to country.

Recently I was in a friend's place discussing problem on excel  spread
sheet and this friend posting a challenge that if I cannot create an
icon button for formatting  "Ringgit Malaysia" I will have to spent a
night away from home. I figured a way of doing it within 10 minutes
and never asked any favor in return.

(1)First I open a blank Excel Workbook, Book1.

(2)Insert the number 123456789.12 in Cell A1.

(3)Click Tools/Macro/Record New Macro and click OK.

(4)Click Format/Cells/Custom/, under the "Type" prompt key
in "RM"#,##0.00. You will notice RM123,456,789.12 displayed  on the
sample column above. Of course you can choose other format  or other
currency symbols if you wish. Then Click OK.

(5)Click Tools/Macro/Stop Recording. Macro1 will be created.

(6)Ensure the Formatting Tool Bar is shown on top, if not click
View/Toolbars/Formatting and the Formatting toolbar will appear on top
with the `$' currency  Style icon.

(7)Click Tools/Customize to pull out the Customize prompt.

(8)Choose Format Categories and scroll Commands until `$' Currency
Style is selected. Click and hold the mouse and drag the selected `$'
selected icon beside the $' Currency Style on the Formatting Toolbar,
i.e. creating a duplicate `$' icon under the Formatting Toolbar.

(9)Click Modified Commands under the Customize Prompt. You can
* Change the Name: to Rinngit Malaysia or your preferred name.
* Assign the Macro1 to this currency style.
* Edit Button Image to suit your taste.

(10)Close the Customize Prompt and you have created a currency style
of your taste.

(11)This extra Currency Style icon will remain in your Excel set up
until you have removed it.