Hi all,
I'm using Excel 2010 and am very new to VBA (halfway through Excel VBA for Dummies 2010). I am trying to create a macro that formats all cells in row 1 that contain text and aligns right all cells with values (text or numbers) below those row 1 cells. My thinking is that it would be nice to have a keyboard shortcut to format the standard spreadsheets that I see in a consistent way.
I would appreciate as much guidance as I can get, but specifically I guess I'm trying to target all cells in row 1 until an empty cell appears and then all cells in the cells below those cells until the cell that is the furthest row down i.e. I don't want to create a procedure that scans all the cells in the worksheet.
I've posted below the code that I'm starting with, which I know is woefully inefficient and insufficient.
Thanks very much in advance for the help!
Sub Align_HeaderColor()
'
' Align_HeaderColor Macro
' Make headers light green and bold, and align them center. Align all text below it to the right.
'
' Keyboard Shortcut: Ctrl+l
'
Range("A1:D1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A2:D9").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
I'm using Excel 2010 and am very new to VBA (halfway through Excel VBA for Dummies 2010). I am trying to create a macro that formats all cells in row 1 that contain text and aligns right all cells with values (text or numbers) below those row 1 cells. My thinking is that it would be nice to have a keyboard shortcut to format the standard spreadsheets that I see in a consistent way.
I would appreciate as much guidance as I can get, but specifically I guess I'm trying to target all cells in row 1 until an empty cell appears and then all cells in the cells below those cells until the cell that is the furthest row down i.e. I don't want to create a procedure that scans all the cells in the worksheet.
I've posted below the code that I'm starting with, which I know is woefully inefficient and insufficient.
Thanks very much in advance for the help!
Sub Align_HeaderColor()
'
' Align_HeaderColor Macro
' Make headers light green and bold, and align them center. Align all text below it to the right.
'
' Keyboard Shortcut: Ctrl+l
'
Range("A1:D1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A2:D9").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub