Weird excel error on Custom formatting please help

£ added to cell in excel despite $ on the custom format using VBA, how to fix?

Im trying to programmatically update cells in Excel but for some reason the VBA code cant programmatically update for $, but can for "£", "€", "¥".

I can use -"$"* #,##0.00-;-"$"* #,##0.00_-;-"$"* "-"??-;_-@ manually in custom format, but when this is added using VBA I get £.

The cells in question have no conditional formatting and have no custom formatting on them as I cleared them all up.

You may suggest to do this using conditional formatting if that works, unfortunately I have hundreds of cells and several CCYs to do so would rather do it smarter then harder.

Sub UpdateCurrencyFormatting()
    Dim currencySymbol As String
    Dim currencyFormat As String
    Dim ws As Worksheet

    ' Define the specific worksheet
    Set ws = ThisWorkbook.Sheets("Step 2. Group Effort")

    ' Get the currency symbol from the helper cell on the specified worksheet
    currencySymbol = ws.Range("J1").Value

    ' Create the custom number format string using the currency symbol
     currencyFormat = "_-""" & currencySymbol & """* #,##0.00_-;-" & _
                 """" & currencySymbol & """* #,##0.00_-;_-" & _
                 """" & currencySymbol & """* ""-""??_-;_-@"

  Debug.Print "Currency Symbol: " & currencySymbol

Debug.Print "Currency Format: " & currencyFormat
    ' Apply the custom number format to the desired range on the specified worksheet
    ws.Range("I6:I7").NumberFormat = currencyFormat
 ws.Range("I9:I11").NumberFormat = currencyFormat
End Sub

The outputs are as follows:

Currency Symbol: £
Currency Format: -"£"* #,##0.00-;-"£"* #,##0.00_-;-"£"* "-"??-;-@
Currency Symbol: €
Currency Format: -"€"* #,##0.00-;-"€"* #,##0.00-;-"€"* "-"??-;-@
Currency Symbol: $
Currency Format: -"$"* #,##0.00-;-"$"* #,##0.00-;-"$"* "-"??-;-@
Currency Symbol: ¥
Currency Format: -"¥"* #,##0.00-;-"¥"* #,##0.00-;-"¥"* "-"??-;_-@

The only one that will not work is $

Cell J1 is a helper cell that I use the following code on:

=CHOOSE(MATCH('Step 3. Pricing Review'!$L$39, {"JPY","EUR","USD","GBP"}, 0), "¥", "€", "$","£")

I have a trigger code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("J1")) Is Nothing Then
Call UpdateCurrencyFormatting
End If
End Sub

So whenever someone changes the Pricing Review'!$L$39 CCY it auto updates Step 2. Group Effort'!$J$1 and the code above fires the autocurrency format update.

As mentioned it works for all but $ and I have tried various techniques including ASCII:

currencyFormat = "_-""" & Chr(36) & """* #,##0.00_-;-""" & Chr(36) & """* #,##0.00_-;_-""" & Chr(36) & """* ""-""??_-;_-@"

Which gives the following:

Currency Symbol: $
Currency Format: -"$"* #,##0.00-;-"$"* #,##0.00_-;-"$"* "-"??-;_-@

Yet still prints £ instead of $, So now I need to reach out and find out if someone has a workaround and can help.

Thanks in advance

I must admit that whilst I put it in ChatGPT a few times, it was a live human that spotted the possible issue that $ was causing.