VBA - Simpler Code for Formatting

Question

I was hoping that there is simpler code for my formatted cells below. The goal of this code is to make a box look a bit nicer, nothing overly fancy, just simple and this code doesn't look simple.

Range("C17:C25").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
Range("A17:C25").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
Range("C17:C25").Select
With Selection.Font
    .ColorIndex = xlAutomatic
    .Name = "Arial"
    .Size = 12
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
End With
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With

I recorded with macros obviously, however, it is very clunky and I am sure that some of it can be taken out, just unsure what can and can't be taken out/modified.

Thanks in advance.

You should check out [how to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251). It'll help a lot. — BruceWayne, Aug 15 '17 at 20:31
If your code works as intended, and is simply poorly written or otherwise feels or *is* inefficient, there's **[codereview.se]** for that. — Mathieu Guindon, Aug 15 '17 at 20:34

score 5 · Accepted Answer · answered Aug 15 '17 at 20:38

Try this. You can dispense with the selects too. Most of the formatting at the end is probably redundant, unless you have previous settings, and looks as if it was generated by the macro recorder.

Sub x()

With Range("C17:C25")
    With .Borders
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With .Font
        .ColorIndex = xlAutomatic
        .Name = "Arial"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    With .Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End With

With Range("A17:C25")
    .BorderAround LineStyle:=xlContinuous, ColorIndex:=0, Weight:=xlMedium
    With .Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
End With

End Sub

@Mat'sMug - thanks. I have just re-arranged the code - has it sorted it? I couldn't see anything wrong and am pretty anal about that sort of thing! — SJR, Aug 15 '17 at 20:40
@SJR, if you want to get picky about formatting, you could indent all of the the body of the sub one more level — jsotola, Aug 15 '17 at 22:33

score 0 · Answer 2 · answered Aug 17 '17 at 16:43

I reformatted it once again to something even simpler. I'd love some feed back to ensure I am not doing anything taboo.

With Range("C17:C25")
    With .Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With .Font
        .ColorIndex = xlAutomatic
        .Name = "Arial"
        .Size = 12
    End With
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    With .Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End With

With Range("A17:C25")
    .BorderAround LineStyle:=xlContinuous, Weight:=xlMedium
    With .Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
End With

VBA - Simpler Code for Formatting

2 Answers2