Change currency format of all currency cells in a workbook based on a dropdown/input

[Legacy 393636]

Hi All,

I've been struggling with this for a while and it would be amazing if I could solve it! Word of warning, My VBA knowledge is limited .

My Goal: To have a cell where you enter (or select from a dropdown) a currency (USD,EUR,GBP etc.) that then changes all currency cells in a workbook to that currency automatically.

I've searched the forum for an answer and I posted in the closest two threads that I've found to what I'm looking for (see below), but still no luck:
*https://www.mrexcel.com/forum/excel...throughout-workbook-based-dropdown-value.html
*https://www.mrexcel.com/forum/excel-questions/973815-replace-formats-across-all-sheets-workbook.html

Currently I have found the below method to change cells (A15:C15) to a particular currency format based on the input to 'C12':

Private Sub Worksheet_Change(ByVal Target As Range)[INDENT]If Intersect(Target, Me.Range("C12")) Is Nothing Then Exit Sub[/INDENT]
[INDENT]On Error GoTo endit[/INDENT]
[INDENT=2]Application.EnableEvents = False[/INDENT]
[INDENT]With Me.Range("A15:C15")[/INDENT]
[INDENT]Select Case Target.Value[/INDENT]
[INDENT=2]Case "USD"[/INDENT]
[INDENT=3].NumberFormat = "[$$-en-US]* #,##0.00;[Red]-([$$-en-US]* #,##0.00);"[/INDENT]
[INDENT=2]Case "GBP"[/INDENT]
[INDENT=3].NumberFormat = "[$£-en-GB]* #,##0.00;[Red]-([$£-en-GB]* #,##0.00);"[/INDENT]
[INDENT=2]Case "EUR"[/INDENT]
[INDENT=3].NumberFormat = "[$€-x-euro2] * #,##0.00;[Red]-([$€-x-euro2] * #,##0.00);"[/INDENT]
[INDENT]End Select[/INDENT]
End With
endit:[INDENT]Application.EnableEvents = True[/INDENT]
End Sub

I hope that this is would work now if I found a way to replace A15:C15 with V<acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">BA</acronym> code that would allow me to select all cells in the workbook if they are currency/accounting formatted.


Cheers!

 

[mole999]

changing the currency display won't convert the values so $1US would be naff if you make it £1GBP. work with a base currency you are familiar with, have a set of standard differences, then you can consider triggering those specifically