Excel Format Cells based on adjacent cell

Question

Good day everyone,

I have a spreadsheet with whole numbers, decimals and percentages, all displaying as decimals, but the users want to see it as properly formatted .

How would I go about changing this using VBA?

To give you an idea of what I have been trying - putting this into Col 14 and 15 respectively, for the Macro to read, as explained in more detail just below:

Sub Macro1()

For Each cell In Range("A2")
' print current number format Debug.Print cell.NumberFormat
' apply a different number format to adjacent cell

' cell one to the right of the active cell
cell.Offset(0, 4).NumberFormat = "@" ' @ means Text
Next

End Sub

Now I have 117 000 rows and 12 columns for this macro to go through, to correct the formats. Col 2 through 13 is for the months in the year, with Col 13 containing text version of the Selection.Style and Col 14 containing the Selection.NumberFormat, which should be used to format the 12 cells to the left of it.

I can't seem to get hold of any sort of Loop to allow for cycling through all columns, and hope that you can assist with this.

Unknown Unknown · Accepted Answer · 2013-09-30T08:10:00

Sub ChangeFormat()
Dim cell As Range
    For Each cell In Range("B1:N" & Range("N" & Rows.Count).End(xlUp).Row)
        cell.NumberFormat = Range("N" & cell.Row).NumberFormat
    Next
End Sub

where cell in the loop will be each cell in the Range("B1:M100").

Excel Format Cells based on adjacent cell

1 Answers