やさしいマクロとVBA

書式設定を一括で変更する

表の罫線、セルのフォントカラー、見出し行の色などの書式設定を一括で設定するマクロの作成を説明します。

上の表を下の表のように、マクロを使って書式設定をします。

目次
  1. データを入力するマクロ
  2. 書式設定を一括で変更するマクロ
  3. テーマ色を確認する

データを入力するマクロ

次のマクロを実行すると、下のような表が作成されます。

　　■　コード内を「Wクリック」で選択できます。

Sub サンプル2120()

    Range("B2") = "品番"
    Range("C2") = "日付"
    Range("D2") = "商品名"
    Range("E2") = "価格"
    Range("F2") = "数量"
    Range("G2") = "小計"
    
    Range("B3") = "A-1"
    Range("B4") = "A-2"
    Range("B5") = "A-3"
    Range("B6") = "A-4"
    Range("B7") = "A-5"

    Range("C3").FormulaR1C1 = "2/1/2020"
    Range("C4").FormulaR1C1 = "2/2/2020"
    Range("C5").FormulaR1C1 = "2/3/2020"
    Range("C6").FormulaR1C1 = "2/4/2020"
    Range("C7").FormulaR1C1 = "2/5/2020"
    
    Range("D3") = "テレビ"
    Range("D4") = "エアコン"
    Range("D5") = "冷蔵庫"
    Range("D6") = "扇風機"
    Range("D7") = "電子レンジ"

    Range("E3") = 80000
    Range("E4") = 120000
    Range("E5") = 210000
    Range("E6") = 5000
    Range("E7") = 30000
  
    Range("F3") = 12
    Range("F4") = 5
    Range("F5") = 6
    Range("F6") = 25
    Range("F7") = 4
    
    Range("G3") = "=E3 * F3"
    Range("G4") = "=E4 * F4"
    Range("G5") = "=E5 * F5"
    Range("G6") = "=E6 * F6"
    Range("G7") = "=E7 * F7"

End Sub

書式設定を一括で変更するマクロ

次のマクロを実行すると、下のように書式が設定されます。

　　■　コード内を「Wクリック」で選択できます。

Sub サンプル2125()

    Dim col As Range

    'グリッド線を非表示
    ActiveWindow.DisplayGridlines = False
    
    
    Range("B2").Select    
    With Selection
        Range("B2:G7").Select
    
        '選択セル範囲に対して書式設定
        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
        
            '各列についての書式を設定        
            For Each col In .Columns
            
              '列内の２つ目の値のデータ型によって書式を設定
              Select Case TypeName(col.Cells(2).Value)
                Case "String"
                    col.HorizontalAlignment = xlLeft
                Case "Double"
                    col.HorizontalAlignment = xlRight
                    col.NumberFormatLocal = "#,###"
                Case "Date"
                    col.HorizontalAlignment = xlCenter
                    col.NumberFormatLocal = "mm/dd"
              End Select
            
              '列幅を自動設定し、それよりも少し大きくする
              col.EntireColumn.AutoFit
              col.ColumnWidth = col.ColumnWidth + 2
            Next
        End With
    End With
    
    Range("B2").Select
    
End Sub
colを変数として宣言します。

Dim col As Range
枠線を非表示にします。(True で表示になります。)

ActiveWindow.DisplayGridlines = False

初めにセルの位置をセルB2にします。

Range("B2").Select
Withで行をまとめます。

With Selection
次にセルB2からG7までの範囲を指定します。

Range("B2:G7").Select
上端に実線で緑の線を引きます。

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

列について繰り返す。

For Each～Case 
Next
列の2番めの値を調べる。

Select Case TypeName(col.Cells(2).Value)
文字列のときは、左寄せにする。

Case "String"
         col.HorizontalAlignment = xlLeft
データ型がダブルのときは、右寄せで３桁区切り。

Case "Double"
           col.HorizontalAlignment = xlRight
           col.NumberFormatLocal = "#,###"
日付のときは、中央揃えで月/日の形にする。

Case "Date"
          col.HorizontalAlignment = xlCenter
　        col.NumberFormatLocal = "mm/dd"
列幅を自動的に調整して、少し大きくする。

col.EntireColumn.AutoFit
              col.ColumnWidth = col.ColumnWidth + 2

テーマ色を確認する

テーマ色を確認するマクロです。

　　■　コード内を「Wクリック」で選択できます。

Sub サンプル2130()

    Range("B2:F2").Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ThemeColor = msoThemeColorAccent1
        .Weight = xlThick
    End With
    
    Range("B4:F4").Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ThemeColor = msoThemeColorAccent2
        .Weight = xlThick
    End With

    Range("B6:F6").Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ThemeColor = msoThemeColorAccent3
        .Weight = xlThick
    End With

    Range("B8:F8").Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ThemeColor = msoThemeColorAccent4
        .Weight = xlThick
    End With

    Range("B10:F10").Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ThemeColor = msoThemeColorAccent5
        .Weight = xlThick
    End With

    Range("B12:F12").Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ThemeColor = msoThemeColorAccent6
        .Weight = xlThick
    End With
     
End Sub

●マクロの実行結果

セルのフォントについては次の記事が参考になります。
■関連記事■　セルのフォントを設定する

以上で「書式設定を一括で変更する」の説明を終わります。