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

この記事では、ExcelVBAを利用して「指定されたフォルダに存在するEXCELに対して、一括で書式設定する」方法について説明しています。

書式設定するEXCELの各列の項目名（ヘッダー項目名）毎に、設定したい書式設定を登録することにより、ボタン1つ押すだけで一括で書式設定を行います。

今回ご紹介する機能を利用すれば、これまで1列ずつ設定していた書式設定をする必要がなくなります。

フォルダにEXCELファイルを格納しておけば、すべてのEXCELに対して各項目名毎に書式設定が適用され、もれなく早くきれいな一覧を作成することができるようになります。

それでは、一括でセルの書式設定する機能を作成していきましょう。

困っている女性

EXCELに入力されている値を1列ずつ書式設定して、きれいな一覧を作成する作業があるの。

困っている女性

毎回同じ書式設定をしているのに、疲れたわ・・・。

困っている女性

ボタン1つ押すだけで、一括で各列毎に書式設定をする方法はないの？

それなら、VBAを使えばボタン1つ押すだけで、EXCELのデータに対して書式設定を一括でできるようになるよ。

解決する男性

しかも、各列毎に設定したい書式設定を登録しておけば、各列の項目名をみて、自動的に書式設定してくれるよ。

解決する男性

困っている女性

それそれ、早く教えてよ。

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

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

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

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

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

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

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

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

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

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

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

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

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

メインシートを作成する

「メイン」という名前のシートを作成します。
A1とB1セルを結合し、「フォルダパス」のタイトル行を作成します。
A2とB2セルを結合し、「フォルダパス」の入力欄を作成します。
A3セルに「項目名」のタイトル行を作成します。
B3セルに「書式」のタイトル行を作成します。
A列の4行目以降は、項目名を入力する欄になります。
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

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

dataNowCol = dataNowCol + 1

Loop

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

wb.Save

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

wb.Close

End If

Set wb = Nothing

MsgBox "完了"

End Sub

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

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

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

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

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

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

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

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

設定可能な書式設定一覧

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

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

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

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

表示形式
配置
フォント
罫線
塗りつぶし
保護

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

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

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

VBAプログラムを実行する

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

③FileSystemObjectを変数に格納する

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

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

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

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

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

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

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

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

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

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

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

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

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

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

⑥対象EXCELブックを開く

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

If nowColName = "" Then

Exit Do

End If

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

shtData.Range(shtData.Cells(2, dataNowCol), _

shtData.Cells(nowMaxRow, dataNowCol)).PasteSpecial Paste:=xlPasteFormats

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

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

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

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

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

⑯列幅を自動調整する

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

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

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

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

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

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

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

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

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

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

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

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

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

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

以上です。

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

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

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

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

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

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

設定可能な書式設定一覧

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

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

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

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

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

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

③FileSystemObjectを変数に格納する

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

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

⑥対象EXCELブックを開く

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

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

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

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

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

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

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

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

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

⑯列幅を自動調整する

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

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

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル