罫線 書式設定②

YouTube VBA

Sub 罫線()
    '実線
    Range("b3:d6").Select
    
    With Selection.Borders
        .LineStyle = True
        .Weight = xlMedium
        .Color = vbRed
    End With
    
    Range("b3").Value = "罫線"
    Selection.Font.Color = vbBlue
    Range("b20").Select
End Sub

Sub 罫線破線()
    '破線
    Range("b3:d6").Select
    Selection.Borders.Weight = xlMedium
    Selection.Borders.LineStyle = xlDash
    Range("b20").Select
    Range("b3").Value = "Keisen"
End Sub

Sub 罫線一点鎖線()
    '一点鎖線
    Range("b3:d6").Select
    With Selection.Borders
        .Weight = xlThick
        .LineStyle = xlDashDot
        '.Weight = xlMedium
        '.Weight = xlThin
        
    End With
    
    Range("b20").Select
End Sub

Sub 罫線点線()
    '点線
    Range("b3:d6").Select
    With Selection.Borders
        .Weight = xlThick
        .LineStyle = xlDot
    End With
    Range("b20").Select
End Sub

Sub 罫線二重線()
    '二重線
    Range("b3:d6").Select
    With Selection.Borders
        '.Weight = xlThin
        .LineStyle = xlDouble
        '.Weight = xlThick
        '.Weight = xlThin
    End With
    Range("b20").Select
End Sub

Sub 罫線斜め破線()
    '斜線
     Range("b3:d6").Select
     With Selection.Borders
        '.Weight = xlThin
        .LineStyle = xlSlantDashDot
        '.Weight = xlMedium
        .Weight = xlThick
        .Color = vbBlack
     End With
     Range("b20").Select
End Sub
Sub 罫線なし()
    '罫線なし
    Range("b3:d6").Select
    With Selection.Borders
        .Weight = xlThin
        .LineStyle = xlLineStyleNone
    End With
    Range("b20").Select
End Sub

Sub 罫線位置指定上()
    '範囲内の上側の罫線
    Range("b3:d6").Select
    Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
    Range("b20").Select
End Sub

Sub 罫線位置指定下()
    '範囲内の下側の罫線
    Range("b3:d6").Select
    Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("b20").Select
End Sub

Sub 罫線位置指定左()
    '範囲内の左端の罫線
    Range("b3:d6").Select
    Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
    Range("b20").Select
End Sub

Sub 罫線位置指定右()
    '範囲内の右端の罫線
    Range("b3:d6").Select
    Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
    Range("b20").Select
End Sub

Sub 罫線位置指定水平()
    '範囲内すべての水平罫線
    Range("b3:d6").Select
    Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous
    Range("b20").Select
End Sub

Sub 罫線位置指定垂直()
    '範囲内すべての垂直罫線
    Range("b3:d6").Select
    Selection.Borders(xlInsideVertical).LineStyle = xlContinuous
    Range("b20").Select
End Sub

Sub 罫線右下がり斜線()
    '右下がり斜線
    Range("b3:d6").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlLineStyleNone
    Range("b20").Select
End Sub

Sub 罫線右上がり斜線()
    '右下がり斜線
    Range("b3:d6").Select
    Selection.Borders(xlDiagonalUp).LineStyle = False
    Range("b20").Select
End Sub
Sub 罫線クリアContents()
    '数式、文字Clear
    Range("b3:d6").Select
    Selection.ClearContents
    Range("b20").Select
End Sub

Sub 罫線クリアClearFormats()
    '書式Clear
    Range("b3:d6").Select
    Selection.ClearFormats
    Range("b20").Select
End Sub

Sub 罫線クリア()
    'Clear
    Range("b3:d6").Select
    Selection.Clear
    Range("b20").Select
End Sub

Sub 罫線クリアtest()
    '罫線、文字、塗りつぶし
    Range("b3:d6").Select
    With Selection
        .Borders.LineStyle = xlContinuous
        '.Borders.Weight = xlMedium
        .Borders(xlDiagonalDown).LineStyle = xlContinuous
        .Borders(xlDiagonalUp).LineStyle = xlContinuous
        .Value = "Test"
        .Interior.Color = vbYellow
    End With
    Range("b20").Select
End Sub
Sub 罫線外枠()
    'BorderAround 太線
    Range("b3:d6").Select
    Selection.BorderAround Weight:=xlThick
    Range("b20").Select
    
    'BorderAround 破線
    Range("b3:d6").Select
    Selection.BorderAround LineStyle:=xlDash
    Range("b20").Select
    
    'BorderAround 太線→破線 Bordersでは破線の細線⑤になる
    Range("b3:d6").Select
    Selection.BorderAround Weight:=xlThick, LineStyle:=xlDash
    Range("b20").Select
    
    'BorderAround 破線→太線 Bordersでは実線の太線⑫になる
    Range("b3:d6").Select
    Selection.BorderAround LineStyle:=xlDash, Weight:=xlThick
    Range("b20").Select
    
    'BorderAround 破線→中線 Bordersでは実線の中線⑪になる
    Range("b3:d6").Select
    Selection.BorderAround LineStyle:=xlDash, Weight:=xlMedium
    Range("b20").Select
    
    '念のためBorderAround 中線→破線 Bordersでは破線の中線⑩になる
    Range("b3:d6").Select
    Selection.BorderAround Weight:=xlMedium, LineStyle:=xlDash
    Range("b20").Select
    
    'BordersでBorderAround 削除
    Range("b3:d6").Select
    Selection.Borders.LineStyle = xlLineStyleNone
    Range("b20").Select
        
    Range("b3:d6").Select
    Selection.BorderAround LineStyle:=xlDash, Weight:=xlMedium
    Range("b20").Select
    
    'BorderAround xlNoneで削除できるか?
    Range("b3:d6").Select
    Selection.BorderAround LineStyle:=xlLineStyleNone
    Range("b20").Select
    
    'BorderAround LineStyle Falseで削除できるか?
    Range("b3:d6").Select
    Selection.BorderAround LineStyle:=False
    Range("b20").Select
    
    'BorderAround LineStyle Trueは使える?
    Range("b3:d6").Select
    Selection.BorderAround LineStyle:=True, Weight:=xlThick
    Range("b20").Select
     
    'BorderAround Trueは使える?
    Range("b3:d6").Select
    Selection.BorderAround True, Weight:=xlMedium
    Range("b20").Select
    
    'BorderAround Falseは使える?
    Range("b3:d6").Select
    Selection.BorderAround False
    Range("b20").Select
    
    '書式Clear
    Range("b3:d6").Select
    Selection.ClearFormats
    Range("b20").Select
         
End Sub
Sub LineStyleWeightCheck()
    Dim ls()
    Dim wt()
    Dim iRow
    Dim iCol
    Dim bs As Borders
    Dim r As Range
    
    ls = Array(xlContinuous, xlDash, xlDashDot, xlDashDotDot, xlDot, xlDouble, xlLineStyleNone, xlSlantDashDot)
    wt = Array(xlHairline, xlThin, xlMedium, xlThick)
    
    Range("c3").Select
    
    For iRow = 0 To UBound(ls) * 2 Step 2
        For iCol = 0 To UBound(wt) * 2 Step 2
            Set r = ActiveCell.Offset(iRow, iCol)
            Set bs = r.Borders
            
            bs.Weight = wt(iCol / 2)
            bs.LineStyle = ls(iRow / 2)
            
            If (bs.LineStyle <> ls(iRow / 2)) Or _
                (bs.Weight <> wt(iCol / 2)) Then
                r.Interior.ColorIndex = 6
            End If
        Next
    Next
End Sub

LineStyleとWeightの組み合わせ可能パターン | Excel作業をVBAで効率化 (vbabeginner.net)
https://vbabeginner.net/linestyle-and-weight-combination-pattern/

コメント