» Format table depending on number formats using VBA in Microsoft Excel

Question:
The columns sheet data are to be formatted according to the number format template in sheet "Formats"


Answer:
Insert the following code in standard module.

Sub Formatting()



   Dim wks As Worksheet

   Dim intCol As Integer

   Dim strCol As String

   Dim txt As String

   Set wks = Worksheets("Formats")

   intCol = 1

   Do Until IsEmpty(wks.Cells(1, intCol))

      txt = wks.Cells(2, intCol)

      Do Until InStr(txt, ",") = 0

         strCol = Left(txt, InStr(txt, ",") - 1)

         Columns(CInt(strCol)).NumberFormat = wks.Cells(1, intCol).Value

         txt = Right(txt, Len(txt) - InStr(txt, ","))

      Loop

      Columns(CInt(txt)).NumberFormat = wks.Cells(1, intCol).Value

      intCol = intCol + 1

   Loop

End Sub