Currency formatting not applying based on each row value

[shotgun1]

Hi

Hoping someone might be able to help.

I'm trying to get the below code to look in column S for the currency code and apply custom format to the relevant ranges. However it doesn't seem to working on individual rows but just taking the first row currency and applying to the whole range.

Would appreciate any help on how to adapt the code to pick the currency code in each row and work accordingly.

Thanks

Sub Macro1()

Dim cl As Range

For Each cl In Intersect(Sheets(1).Range("G7:M50"), Sheets(1).UsedRange)

   Select Case cl.Offset(0, 6).Text
   
        Case "EUR": Range("G7:M50,o7:o50").NumberFormat = "€#,##0.00;(€#,##0.00)"
        Case "GBP": Range("G7:M50,O7:O50").NumberFormat = "£#,##0.00;(£#,##0.00)"
        Case "USD": Range("G7:M50,O7:O50").NumberFormat = "$#,##0.00;($#,##0.00)"
        Case "DKK": Range("G7:M50,O7:O50").NumberFormat = "DKK#,##0.00;(DKK#,##0.00)"
        Case "SEK": Range("G7:M50,O7:O50").NumberFormat = "SEK#,##0.00;(SEK#,##0.00)"
        Case "CZK": Range("G7:M50,O7:O50").NumberFormat = "CZK#,##0.00;(CZK#,##0.00)"
        Case "RUB": Range("G7:M50,O7:O50").NumberFormat = "RUB#,##0.00;(RUB#,##0.00)"
        Case "TRY": Range("G7:M50,O7:O50").NumberFormat = "TRY#,##0.00;(TRY#,##0.00)"
        Case "EGP": Range("G7:M50,O7:O50").NumberFormat = "EGP#,##0.00;(EGP#,##0.00)"
        Case "CHF": Range("G7:M50,O7:O50").NumberFormat = "CHF#,##0.00;(CHF#,##0.00)"
        Case "INR": Range("G7:M50,O7:O50").NumberFormat = "INR#,##0.00;(INR#,##0.00)"
        Case "PLN": Range("G7:M50,O7:O50").NumberFormat = "PLN#,##0.00;(PLN#,##0.00)"
        Case "RSD": Range("G7:M50,O7:O50").NumberFormat = "RSD#,##0.00;(RSD#,##0.00)"
       
   End Select

Next cl

End Sub

 

[Hui]

What is not right with the above code?

can you attach a file with a sample of what is wrong ?

 

[YasserKhalil]

May be

Sub Test()
    Dim cl As Range, rng As Range
   
    For Each cl In Intersect(Sheets(1).Range("G7:M50"), Sheets(1).UsedRange) 'Range("G7:G50")
        Set rng = Range("G" & cl.Row & ":M" & cl.Row & ",o" & cl.Row & ":o" & cl.Row & "")
        Select Case cl.Offset(0, 6).Text
            Case "EUR": rng.NumberFormat = "€#,##0.00;(€#,##0.00)"
            Case "GBP": rng.NumberFormat = "£#,##0.00;(£#,##0.00)"
            Case "USD": rng.NumberFormat = "$#,##0.00;($#,##0.00)"
            Case "DKK": rng.NumberFormat = "DKK#,##0.00;(DKK#,##0.00)"
            Case "SEK": rng.NumberFormat = "SEK#,##0.00;(SEK#,##0.00)"
            Case "CZK": rng.NumberFormat = "CZK#,##0.00;(CZK#,##0.00)"
            Case "RUB": rng.NumberFormat = "RUB#,##0.00;(RUB#,##0.00)"
            Case "TRY": rng.NumberFormat = "TRY#,##0.00;(TRY#,##0.00)"
            Case "EGP": rng.NumberFormat = "EGP#,##0.00;(EGP#,##0.00)"
            Case "CHF": rng.NumberFormat = "CHF#,##0.00;(CHF#,##0.00)"
            Case "INR": rng.NumberFormat = "INR#,##0.00;(INR#,##0.00)"
            Case "PLN": rng.NumberFormat = "PLN#,##0.00;(PLN#,##0.00)"
            Case "RSD": rng.NumberFormat = "RSD#,##0.00;(RSD#,##0.00)"
        End Select
    Next cl
   
End Sub

 

[shotgun1]

What is not right with the above code?

can you attach a file with a sample of what is wrong ?

Hi Hui

This is how the output is coming out based on the code I pasted.

Using YasserKhalil's updated code it's appearing to update on a line by line basis but for some reason the USD lines are showing a £ symbol which I can't fathom.

The other problem that's arising is that for all the non-symbol currencies from DKK onwards I'm getting the following error when I run it:
Unable to set the NumberFormat property of the Range class

How could I update the NumberFormat to resolve this at all?