Dave T
06-01-2016, 04:39 PM
Hello All,
I have asked a longer version of this question in another forum (http://www.msofficeforums.com/excel-programming/31352-format-range-cells.html), but as I have had no responses so far I thought I would ask for a much simpler solution.
I have a workbook which is created as an output from another program. I use a macro that creates a new worksheet and then copies specific columns and rearranges them in the new worksheet.
I also use the following macro to format the new worksheet, but it feels a bit long winded to me. The macro I am using keeps repeating With Worksheets("Sheet2") for the formatting of each column and I wonder if this is necessary.
How can I just select a range of cells to be formatted that are in column A and the macro formats this selection, then formats the adjacent column (B) and then the next (C, D, E and so on).
I have several ranges of data each with a different header so I am after selected cells and not .Range("A3:A" & LASTROW).Select.
Sub FormatColumns()
Dim LASTROW As Long
With Worksheets("Sheet2")
LASTROW = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("A3:A" & LASTROW).Select
With Selection.Font
.Name = "Calibri" 'Change font type here
.Bold = False
.Size = 10 'Change font size here
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.ColumnWidth = 6
.Rows.AutoFit
End With
End With
With Worksheets("Sheet2")
LASTROW = .Cells(Rows.Count, "B").End(xlUp).Row
.Range("B3:B" & LASTROW).Select
With Selection.Font
.Name = "Calibri"
.Bold = False
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.ColumnWidth = 10
.Rows.AutoFit
End With
End With
End Sub
If someone has a better idea how to format columns adjacent of the selected cells in column A it would be greatly appreciated.
Hopefully I just need a macro that shows how to format columns A, B and C and I can add the extra columns myself.
Regards,
Dave T
I have asked a longer version of this question in another forum (http://www.msofficeforums.com/excel-programming/31352-format-range-cells.html), but as I have had no responses so far I thought I would ask for a much simpler solution.
I have a workbook which is created as an output from another program. I use a macro that creates a new worksheet and then copies specific columns and rearranges them in the new worksheet.
I also use the following macro to format the new worksheet, but it feels a bit long winded to me. The macro I am using keeps repeating With Worksheets("Sheet2") for the formatting of each column and I wonder if this is necessary.
How can I just select a range of cells to be formatted that are in column A and the macro formats this selection, then formats the adjacent column (B) and then the next (C, D, E and so on).
I have several ranges of data each with a different header so I am after selected cells and not .Range("A3:A" & LASTROW).Select.
Sub FormatColumns()
Dim LASTROW As Long
With Worksheets("Sheet2")
LASTROW = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("A3:A" & LASTROW).Select
With Selection.Font
.Name = "Calibri" 'Change font type here
.Bold = False
.Size = 10 'Change font size here
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.ColumnWidth = 6
.Rows.AutoFit
End With
End With
With Worksheets("Sheet2")
LASTROW = .Cells(Rows.Count, "B").End(xlUp).Row
.Range("B3:B" & LASTROW).Select
With Selection.Font
.Name = "Calibri"
.Bold = False
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.ColumnWidth = 10
.Rows.AutoFit
End With
End With
End Sub
If someone has a better idea how to format columns adjacent of the selected cells in column A it would be greatly appreciated.
Hopefully I just need a macro that shows how to format columns A, B and C and I can add the extra columns myself.
Regards,
Dave T