VBA if row 1 has text then format those row 1 cells and cells below in certain way

[DryFly]

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

 

[mumps]

Try:

Sub FormatCells()
    Application.ScreenUpdating = False
    Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    With Range(Cells(1, 1), Cells(1, lCol))
        .Interior.ColorIndex = 4
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
    End With
    ActiveSheet.UsedRange.Offset(1, 0).HorizontalAlignment = xlRight
    Application.ScreenUpdating = True
End Sub

 

[mole999]

Sheets do have a consistent format, numbers aligned right. text aligned , that when you start out helps to identify text numbers

 

[JLGWhiz]

When setting parameters with VBA, it is not necessary to list the default parameters. You only need to list the mandatory arguments and any optional arguments that you want which are different from the default values. Code written with the recorder will list all the parameters and you have to edit out those you do not need.