Option Explicit
'by Hernan Torres (Mikel ERP)
'December 25, 2019
'Refer to several sheets
'Question by vishukm86
'topic: https://www.mrexcel.com/board/threads/change-currency-symbol-for-the-entire-worksheet-with-a-drop-down-currency-list.1118810/
Sub FormatCells(currSelected As String)
Const skipRows As Long = 11 'for skip eleven rows
Dim b(), c(), myCurrFormat 'b for layout, c for companies
Dim pinCell As Range
Dim l As Long, h As Long, i As Long, j As Long, k As Long 'l=low limit row; h=high limit row; j=jumps between company ranges; k=qty companies
Dim myCurrency As String, rngValues As String, rngTotal As String, rngVendors As String
Application.ScreenUpdating = False
myCurrency = Application.Index(Range("parameters"), Application.Match(currSelected, Range("parameters[Currency]"), 0), 2)
myCurrFormat = "\" & myCurrency & " #,##0.#0;\" & myCurrency & " -#,##0.#0"
Sheets("Do not disturb").Activate
c = Sheets("Do not disturb").Range("D3:D" & Range("D" & Rows.Count).End(xlUp).Row).Value2
k = UBound(c)
Sheets("Price Comparision").Activate
b = Sheets("Price Comparision").Range("G12:AQ" & Range("G" & Rows.Count).End(xlUp).Row).Value2
l = LBound(b) + skipRows
h = UBound(b) + skipRows
Range(Cells(l, 7), Cells(h, 10)).Select
Selection.NumberFormat = myCurrFormat
Set pinCell = Cells(l, 11).Offset(0, 3)
For i = 1 To UBound(c) 'for each company
rngValues = Range(Cells(l, pinCell.Column + j), Cells(h, pinCell.Offset(0, 2).Column + j)).Address(RowAbsolute:=False, ColumnAbsolute:=False, ReferenceStyle:=xlA1)
rngTotal = Range(Cells(l, pinCell.Offset(0, j + 4).Column), Cells(h + 3, pinCell.Offset(0, j + 4).Column)).Address(RowAbsolute:=False, ColumnAbsolute:=False, ReferenceStyle:=xlA1)
rngVendors = Range(Cells(6, pinCell.Column + j).Address).Address(RowAbsolute:=False, ColumnAbsolute:=False, ReferenceStyle:=xlA1) 'total for each vendors
Range(rngValues & "," & rngTotal & "," & rngVendors).Select
Selection.NumberFormat = myCurrFormat
'Debug.Print c(i, 1)
j = j + 5
Next i
Application.ScreenUpdating = True
Range("I3").Activate
End Sub
