EXCEL/VBA

26_書式を一括設定

  / 
Sub 書式を一括設定()
    
    'グリッド線を非表示
    ActiveWindow.DisplayGridlines = False
    
    '選択セル範囲に対して書式設定
    With Selection
        '上端・下端に実線
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ThemeColor = msoThemeColorAccent6
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ThemeColor = msoThemeColorAccent6
            .Weight = xlMedium
        End With
        
        '行方向に点線
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlDot
            .ThemeColor = msoThemeColorAccent6
            .Weight = xlThin
        End With
        
        '数式セルのフォントカラー変更
        .SpecialCells(xlCellTypeFormulas).Font.ThemeColor = msoThemeColorAccent5
            
        '１行目（見出し行）の色を設定
        With .Rows(1).Interior
            .ThemeColor = msoThemeColorAccent6
            .TintAndShade = 0.5
        End With
        
        '各列についての書式を設定
        Dim colRng As Range
        For Each colRng In .Columns
            '列内の２つ目の値のデータ型によって書式を設定
            Select Case TypeName(colRng.Cells(2).Value)
                Case "String"
                    colRng.HorizontalAlignment = xlLeft
                Case "Double"
                    colRng.HorizontalAlignment = xlRight
                    colRng.NumberFormatLocal = "#,###"
                Case "Date"
                    colRng.HorizontalAlignment = xlRight
                    colRng.NumberFormatLocal = "mm/dd"
            End Select
            
            '列幅を自動設定し、それよりも少し大きくする
            colRng.EntireColumn.AutoFit
            colRng.ColumnWidth = colRng.ColumnWidth + 2
        Next
    End With

End Sub
RELATED POST