Currency formatting based on three letter code in cell

[albasheer]

Hello,

How can I custom format a cell to display the currency three-letter formatting based on an input in another cell.

For example in Cell A1 I would enter ZAR and the format would be applied to a certain range.

I am thinking it would be some sort of code applied to a button to press after setting the currency.

The custom format is _([$SAR] * #,##0.00_);_([$SAR] * (#,##0.00);_([$SAR] * "-"??_);_(@_)

Thank you.

 

[rs2k]

how are you running the code?

 

[PuckMark]

Here is my entire code that I have embedded in this particular worksheet. The top few sections are now working perfectly, thanks to the help of other members on this board. What I am now attempting to do is to allow for user input (through data validation) of 40+ different currencies in cell K7, and then format a range of cells based on that input.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect
Application.EnableEvents = False
    If Target.Address(False, False) = "C7" Then Call Changeto1
    If Not Intersect(Range("c9"), Target) Is Nothing Then
    Rows("15:44").EntireRow.Hidden = True
    Rows("15:" & Range("c9").Value + 14).EntireRow.Hidden = False
    End If
Application.EnableEvents = True
Me.Protect
End Sub
 
Private Sub Worksheet_Calculate()
Me.Unprotect
    If Range("c7").Value = "Single SKU" Then
    Dim MySheet As Worksheet
    Dim MyRange As Range
    Set MySheet = ActiveSheet
    Set MyRange = ActiveCell
    Call Changeto1
    MySheet.Select
    MyRange.Select
End If
Me.Protect
End Sub
 
Private Sub CurrFormat()
Select Case Range("K7").Text
Case "USD - United States US Dollar"
Range("C82:C89").Select Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
End Select
End Sub

 

[rs2k]

I cant see how you are running this (CurrFormat) bit of code? change event? button?

 

[PuckMark]

I should have mentioned that I'm a complete VBA beginner, so I'm probably not doing this the right way. What I have is a drop down (validation list) in cell K7 that has all 40+ currencies listed. What I WANT to have happen is that when the currency is selected, range C82:C89 automatically changes to the right format. I've only included the USD option right now, but would eventually write the code to include all the currency formatting options. I figured if I could get this one to work, the rest would be similar, but I can't get this one to do anything.

Any help would be MUCH appreciated.

 

[PuckMark]

Ok, I figured it out. Thanks to your question, I realized I needed to put this under the Worksheet_Change event, and it worked beautifully. Thanks for your help.