Hi ALL,
i found this code that changes all cells formatted as currency in a workbook to what is in a select list value (G4).I am experiencing the problem that when the select list changes from one value to the next there is activity but the cells that are supposed to be changed form say R1234.00 to
£1234.00 are not changing. When I right click on a cell that is supposed to have been changed I see they are now formatted as Number?
This is the VBA I am using in Excel 2016.
I hope someone can help me here as searched and searched but getting no-where...
Code
Private Sub Worksheet_Change(ByVal Target As Range)
Dim objStyle As Style
If Not Intersect(Target, Range("G4")) Is Nothing Then
Set objStyle = ThisWorkbook.Styles("Currency")
Select Case Range("G4").Value
Case "USD"
objStyle.NumberFormat = "[$$-409]#,##0_ ;-[$$-409]#,##0"
Case "EUR"
objStyle.NumberFormat = "[$€-2] #,##0;-[$€-2] #,##0"
Case "R"
objStyle.NumberFormat = "[$R-2] #,##0;-[$R-2] #,##0"
Case "GBP"
objStyle.NumberFormat = "[$£-2] #,##0;-[$£-2] #,##0"
End Select
End If
End Sub
Display More