Borders and Formatting on VBA MS.Excel

 

Assalamualaikum wr.wb
Borders seem very complicated when you record a macro for borders, do any kind of text of text formatting, etc., and look at it in Visual Basic. There are a few ways to simplify this. 



Border Formatting Example
You record a macro, make a selection, and set borders. You immediately get something like this:

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 WithEnd with ...

Borders(Direction) indicate which border you want changed. 

xlDiagonalDown affects the line from the top-left to bottom-right of each cell selected.
xlDiagonalUp affects the line from the bottom-left to top-right of each cell selected.
xlEdgeLeft, xlEdgeRight, xlEdgeUp, and xlEdgeDownaffects the left, right, top, and bottom borders of your selection. That means it treats your selection as a box, and the sides are what the code affects, see the above image for details.
xlInsideVertical and xlInsideHorizontal affects all the other vertical and horizontal lines (respectively) in your selection that the above didn't cover.
If you don't specify a direction, then all

The parameters you can set for borders are:

.LineStyle controls the line style. See the top for details.
.Weight controls how thick the line will be.
.Color controls the color of the line. You can use RGB format.
.TintAndShade controls tint and shade, but they can be ignored if you're using RGB values.

You don't have to stick to just the current selection, it can be modified so that you can change the borders for any cell without having to select it (and there are some good uses for this), so you could substitute Selection with stuff like: 

With Worksheets("Sheet1").Cells(6, 1)                   
        .Borders(xlEdgeBottom).LineStyle = xlContinousV End With

- OR - 

With [D2].Borders(xlEdgeTop)
.       LineStyle = xlContinous
       .Color = RGB (255,0,0)
End With

Text Formatting

.Color controls the color of the test (use RGB)
.PatternColor controls the color of the background (use RGB)
.HorizontalAlignment controls text horizontal alignment (equal to xlLeft, xlRight, or xlCenter)
.HorizontalAlignment controls text horizontal alignment (equal to xlTop, xlCenter, or xlBottom)
.WrapText = (true to wrap)
.ShrinkToFit = (true to apply)
.Orientation = (what angle to rotate it by)
.AddIndent = (true to indent)
.IndentLevel to determine how far to indent (whole numbers only)
.MergeCells to merge or remove the merge on the affected cell(s). To not do anything with the cells structure, don't include it.
.Name = (font name, enclosed in quotes)
.Size = (font size)
.Strikethrough = (True to make it strikethrough)
.Superscript = (True to make it superscript)
.Subscript = (True to make it subscript)
.OutlineFont = (True to put an outline on text, but it apparently does nothing)
.Shadow = (True to put a shadow on text, but it apparently does nothing) 


Contoh Fungsi Untuk membuat garis
Sub xlBorders(xlRange As Range, OutLineWeight As String, InLineWeight As String, _
                       Optional OutLineColor As Long = -4105, _
                       Optional InLineColor As Long = -4105)
'Call xlBorders(Worksheets("Sheet1").Range("A1:J10"), "thin", "hairline", 5, 5)
Dim OutLineStyle As Variant
Dim OutLineWt As Variant
Dim InLineStyle As Variant
Dim InLineWt As Variant

OutLineStyle = xlContinuous
Select Case LCase(OutLineWeight)
Case "hairline"
          OutLineWt = xlHairline
Case "thin"
         OutLineWt = xlThin
Case "medium"
         OutLineWt = xlMedium
Case "thick"
          OutLineWt = xlThick
Case "none"
         OutLineStyle = xlNone
Case Else
         MsgBox "ERROR: bad value for OutLineWeight", _
         vbCritical, "xlBorders"
         Exit Sub
End Select
InLineStyle = xlContinuous
Select Case LCase(InLineWeight)
Case "hairline"
          InLineWt = xlHairline
Case "thin"
          InLineWt = xlThin
Case "medium"
          InLineWt = xlMedium
Case "thick"
           InLineWt = xlThick
Case "none"
           InLineStyle = xlNone
Case Else
          MsgBox "ERROR: bad value for InLineWeight", _
          vbCritical, "xlBorders"
          Exit Sub
End Select

On Error Resume Next
xlRange.Borders(xlDiagonalDown).LineStyle = xlNone
xlRange.Borders(xlDiagonalUp).LineStyle = xlNone
With xlRange.Borders(xlEdgeLeft)
        .LineStyle = OutLineStyle
        .Weight = OutLineWt
        .ColorIndex = OutLineColor
End With
With xlRange.Borders(xlEdgeTop)
        .LineStyle = OutLineStyle
        .Weight = OutLineWt
        .ColorIndex = OutLineColor
End With
With xlRange.Borders(xlEdgeBottom)
        .LineStyle = OutLineStyle
        .Weight = OutLineWt
        .ColorIndex = OutLineColor
End With
With xlRange.Borders(xlEdgeRight)
        .LineStyle = OutLineStyle
       .Weight = OutLineWt
       .ColorIndex = OutLineColor
End With
With xlRange.Borders(xlInsideVertical)
        .LineStyle = InLineStyle
       .Weight = InLineWt
       .ColorIndex = InLineColor
End With
With xlRange.Borders(xlInsideHorizontal)
        .LineStyle = InLineStyle
       .Weight = InLineWt
       .ColorIndex = InLineColor
End With
End Sub