【EXCEL：VBA】一括でセルの書式設定がしたい

概要図（一括でセルの書式設定をする機能）

今回作成する「一括でセルの書式設定をする機能」の概要図は、以下です。

一括でセルの書式設定をする機能の概要図

フォルダパスと各項目毎の書式設定を指定しておけば、指定されたフォルダパスに存在するEXCELファイル全てに、一括で書式設定します。

◆他にも、一括で面倒な作業をボタン1つ押すだけでやってくれる機能については、以下もありますよ。

【EXCEL：VBA】一括で大量にフォルダを作成したい

【EXCEL：VBA】フォルダに存在するCSVファイルを全て取り込みたい

【EXCEL：VBA】サブフォルダを含む全てのフォルダやファイルの情報を取得したい

それでは、一括でセルの書式設定をする機能を、1つ1つ順番に説明していきます。

フォルダパス指定欄、書式設定したい項目名と設定して欲しい書式を設定する欄を準備する

まず、EXCELファイルが格納されているフォルダを指定する欄を作成します。

次に、項目名と設定して欲しい書式を登録する欄を作成します。

「メイン」シートを作成する

メインシートを作成する

1. 「メイン」という名前のシートを作成します。
2. A1とB1セルを結合し、「フォルダパス」のタイトル行を作成します。
3. A2とB2セルを結合し、「フォルダパス」の入力欄を作成します。
4. A3セルに「項目名」のタイトル行を作成します。
5. B3セルに「書式」のタイトル行を作成します。
6. A列の4行目以降は、項目名を入力する欄になります。
7. B列の4行目以降は、具体的に書式設定する欄になります。

VBAで入力規則の情報を取得する機能を作成する

開発タブの「Visual Basic」をクリックして、「Microsoft Visual Basic for Applications」を起動します。

Microsoft Visual Basic for Applicationsを起動する

「Microsoft Visual Basic for Applications」にVBAプログラムを入力します。

VBAプログラムを入力する（その１）

VBAプログラムを入力する（その２）

標準モジュールを追加して、以下のソースを書きます。

Public Sub MainProc()
    Dim shtMain As Worksheet
    Dim shtData As Worksheet
    Dim fso As Object
    Dim f As Object
    Dim wb As Workbook
    Dim setteiMaxRow As Long
    Dim dataNowCol As Long
    Dim nowColName As String
    Dim nowMaxRow As Long
    Dim i As Long
    
    '①メインシートを変数に格納する
    Set shtMain = ThisWorkbook.Sheets("メイン")
    
    '②書式設定が入力されているA列の最終行を取得する
    setteiMaxRow = shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row
    
    '③FileSystemObjectを変数に格納する
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    '④指定されているフォルダに存在するファイル数分処理を繰り返す
    For Each f In fso.GetFolder(shtMain.Range("A2")).Files
    
        '⑤拡張子が「.xlsx」のみ対象とする
        If LCase(fso.getextensionName(f.Name)) = "xlsx" Then
            
            '⑥対象ブックを開く
            Set wb = Workbooks.Open(f.Path)
            
            '⑦一番左のシートを変数に格納する
            Set shtData = wb.Sheets(1)
            
            dataNowCol = 1
            
            Do While True
                
                '⑧1行目の項目名を取得する
                nowColName = shtData.Cells(1, dataNowCol)
                
                '⑨データ入力されている最終行を取得する
                nowMaxRow = shtData.Cells(shtData.Rows.Count, dataNowCol).End(xlUp).Row
                
                '⑩項目名が空ならループを抜ける
                If nowColName = "" Then
                    Exit Do
                End If
                
                '⑪書式設定が入力されている4行目から最終行までループする
                For i = 4 To setteiMaxRow
                
                    '⑫書式設定が入力されている項目名と同じ項目名の場合、以下の処理を行う
                    If shtMain.Cells(i, 1) = nowColName Then
                    
                        '⑬書式設定（B列）をコピーする
                        shtMain.Cells(i, 2).Copy
                        
                        '⑭該当列の2行目から最終行を対象に、書式をコピーする
                        shtData.Range(shtData.Cells(2, dataNowCol), _
                                      shtData.Cells(nowMaxRow, dataNowCol)).PasteSpecial Paste:=xlPasteFormats
                        
                        '⑮書式コピーモードを抜ける
                        Application.CutCopyMode = False
                        
                        '⑯列幅を自動調整する
                        shtData.Columns(dataNowCol).AutoFit
                        
                        shtData.Cells(1, 1).Select
                        
                        Exit For
                    End If
                Next
                
                dataNowCol = dataNowCol + 1
            Loop
            
            '⑰対象EXCELファイルを保存する
            wb.Save
            
            '⑱対象EXCELファイルを閉じる
            wb.Close
            
        End If
    Next
        
    Set wb = Nothing
    
    MsgBox "完了"
    
End Sub

最後にEXCELを「◯◯◯.xlsm」というような感じでマクロ有効ブック形式で保存します。

フォルダに存在するEXCELファイルに一括で書式設定する機能の使い方

それでは、EXCELファイルに一括で書式設定する方法を説明します。

「フォルダパス」と「項目名」と「書式」を入力する

まず、A2セルにEXCELファイルが格納されているフォルダのパスを入力します。

次に、A列の4行目以降に「項目名」を入力します。

A列に入力した「項目名」の値に設定したい「書式」をB列に入力します。

一括で書式設定する機能の使い方

設定可能な書式設定一覧

B列の書式に設定できる「書式設定」について、説明します。

ずばり、セルの書式設定ダイアログで設定できる項目になります。

ちなみに、セルの書式設定ダイアログは、「CTRL」ボタン＋数字「1」ボタンで起動することができます。

具体的には、以下の６種類が設定できます。

1. 表示形式
2. 配置
3. フォント
4. 罫線
5. 塗りつぶし
6. 保護

セルの書式設定（表示形式）

「一括で書式設定する」マクロを実行する

次に、「開発」タブの「マクロ」をクリックします。

VBAプログラムを実行する

先程作成した自作関数「MainProc」が一覧に表示されていますので、選択します。

次に、「実行」ボタンをクリックします。

実行ボタンをクリックする

しばらく待つと、指定したフォルダに存在するEXCELファイルに対して、書式設定が適用されます。

ちなみに、EXCELファイルの一番左のシートのみに書式設定が適用されますので、ご注意ください。

書式設定される前のEXCELファイルの例

以下のEXCELファイルは、書式設定される前のEXCELファイルです。

書式設定される前のEXCELファイル

書式設定された後のEXCELファイルの例

ちなみに、各列の幅は見やすくするために、列幅を自動的に調整されます。

書式設定された後のEXCELファイル

EXCELファイルに一括で書式設定するVBAプログラム（MainProc）の説明

それでは、VBAプログラム（MainProc）を説明します。

①参照するシートを変数に格納する

'①メインシートを変数に格納する
Set shtMain = ThisWorkbook.Sheets("メイン")

ここでは、このあとの処理で何度も使う「メイン」シートを変数に格納しています。

そうすることにより、入力するプログラムを短くすることができるからです。

何度も「ThisWorkbook.Sheets(“メイン”)」と入力するより、「shtMain」と入力するほうがコードが少なくてよいからです。

②書式設定が入力されているA列の最終行を取得する

'②書式設定が入力されているA列の最終行を取得する
setteiMaxRow = shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row

ここでは、A列の入力されている最終行を変数に格納しています。

書式設定したいEXCELの項目名（ヘッダー項目名）が、書式設定されているか調べる範囲を特定するためにやっています。

③FileSystemObjectを変数に格納する

'③FileSystemObjectを変数に格納する
Set fso = CreateObject("Scripting.FileSystemObject")

ここでは、フォルダに存在するファイル一覧を取得する機能をもったFileSystemObjectを準備しています。

FileSystemObjectは、他にも便利な機能があります。

④指定されているフォルダに存在するファイル数分処理を繰り返す

'④指定されているフォルダに存在するファイル数分処理を繰り返す
For Each f In fso.GetFolder(shtMain.Range("A2")).Files

ここでは、A2セルに入力されているフォルダパスに存在するファイル数分、この後の処理を繰り返します。

フォルダに存在するファイルを1つずつ取得し、変数「f」に格納しています。

⑤拡張子が「.xlsx」のみ対象とする

'⑤拡張子が「.xlsx」のみ対象とする
If LCase(fso.getextensionName(f.Name)) = "xlsx" Then

ここでは、取得したファイルの拡張子が「xlsx」かチェックしています。

拡張子が「xlsx」の場合のみ、この後の処理を実行します。

⑥対象EXCELブックを開く

'⑥対象ブックを開く
Set wb = Workbooks.Open(f.Path)

ここでは、取得したEXCELファイルを開き、変数「wb」に格納しています。

⑦一番左のシートを変数に格納する

'⑦一番左のシートを変数に格納する
Set shtData = wb.Sheets(1)

ここでは、開いたEXCELファイルの一番左のシートを変数「shtData」に格納しています。

⑧1行目の項目名を取得する

'⑧1行目の項目名を取得する
nowColName = shtData.Cells(1, dataNowCol)

ここでは、取得したEXCELシートの1行目の各列の値を変数「nowColName」に格納しています。

書式設定が登録されているか項目名と同じか調べるために、各列の項目名を取得しています。

⑨データ入力されている最終行を取得する

'⑨データ入力されている最終行を取得する
nowMaxRow = shtData.Cells(shtData.Rows.Count, dataNowCol).End(xlUp).Row

ここでは、現在の列にデータが入力されている最終行を取得し、変数「nowMaxRow」に格納しています。

書式設定する範囲を指定するために、最終行を取得します。

⑩項目名が空ならループを抜ける

'⑩項目名が空ならループを抜ける
If nowColName = "" Then
    Exit Do
End If

ここでは、取得した列の項目名が空の場合、ループを抜けます。

値が入力されている最終列まで書式設定されます。

⑪書式設定が入力されている4行目から最終行までループする

'⑪書式設定が入力されている4行目から最終行までループする
For i = 4 To setteiMaxRow

ここでは、「メインシート」に設定されている書式設定を4行目から最終行まで繰り返します。

⑫書式設定が入力されている項目名と同じ項目名の場合、以下の処理を行う

'⑫書式設定が入力されている項目名と同じ項目名の場合、以下の処理を行う
If shtMain.Cells(i, 1) = nowColName Then

ここでは、取得した項目名が「メインシート」に設定されている項目名と同じか確認しています。

項目名が同じ場合、「メインシート」のB列に設定されている書式設定を利用します。

⑬書式設定（B列）をコピーする

'⑬書式設定（B列）をコピーする
shtMain.Cells(i, 2).Copy

ここでは、「メインシート」のB列に設定されている書式設定をコピーしています。

⑭該当列の2行目から最終行を対象に、書式をコピーする

'⑭該当列の2行目から最終行を対象に、書式をコピーする
shtData.Range(shtData.Cells(2, dataNowCol), _
              shtData.Cells(nowMaxRow, dataNowCol)).PasteSpecial Paste:=xlPasteFormats

ここでは、書式設定するEXCELシートの該当列の2行目から値が入力されている最終行を対象に、コピーした書式設定を貼り付けています。

⑮書式コピーモードを抜ける

'⑮書式コピーモードを抜ける
Application.CutCopyMode = False

ここでは、「メインシート」の書式設定をコピーするモードを抜けます。

⑯列幅を自動調整する

'⑯列幅を自動調整する
shtData.Columns(dataNowCol).AutoFit

ここでは、該当列の列幅を自動調整しています。

入力されている値を見やすくするために、列幅を自動調整します。

⑰対象EXCELファイルを保存する

'⑰対象EXCELファイルを保存する
wb.Save

ここでは、書式設定したEXCELファイルを上書き保存しています。

⑱対象EXCELファイルを閉じる

'⑱対象EXCELファイルを閉じる
wb.Close

ここでは、書式設定したEXCELファイルを閉じています。

結果として、指定されたフォルダに存在するEXCELファイル全てに対して、「メインシート」に登録されている書式設定を反映することができるようになりました。

それでは、各項目名毎に反映させたい書式設定を登録し、面倒で退屈な書式設定作業を楽しましょう。

以上です。

最後まで読んでいただきありがとうございました。
この記事をシェアしていただけると喜びます。