エクセルVBAの書式設定完全ガイド！色・条件付き書式まで

初心者

エクセルVBAの書式設定で、表をラクに作りたい！

Dr.オフィス

VBAを一度作ってしまえば、書式設定の繰り返しから解放されるよ！

この記事では、手動だと意外と手間がかかる「書式設定」について、エクセルVBAで一括設定する方法をご紹介します。

次の5つのコードがよく使われますので、まずはこの5つを押さえておきましょう。

これらのコードに加えて、FormatConditionsで条件付き書式を設定することで、入力内容に応じて書式を自動で変えられます。

コピペで使えるサンプルコードも活用しながら、一緒に試してみましょう！

書式設定以外にも、覚えておくと役立つエクセルVBAの便利ワザについて、以下の記事で解説しています。

＼ Officeドクター読者限定・無料Q&A ／

この記事の内容でわからないことや、今すぐ解決したいOfficeのお悩みはありませんか？
Officeドクターの中の人が、公式LINEで直接ご質問にお答えします！
下のボタンをタップして、表示された入力欄からそのまま送信してくださいね。

LINEでこの記事について質問する

＼ スマホのLINEアプリからご質問ください！ ／

スマホのカメラで上のQRコードを読み取ると、
この記事が開くのでLINEで質問するボタンをタップしてください。

エクセルVBAの書式設定はプロパティを変えるだけでOK

エクセルVBAで書式設定をすることで、地味に手間のかかるリスト作成が一括で完了します。

今回は、実務でありがちな「社員証再発行リスト」を例に、VBAで書式設定する方法を一緒に確認していきましょう。

VBAで書式設定する方法は、書式を変えたいセルを指定して、変えたい内容に応じたコードに値を入れるだけです。

書式に関わるコードは、主に次の4つです。

まずは、試しに、A1セルを黄色に塗るコードを書いてみましょう。

STEP

「Visual Basic」を押す

1. 「開発」タブをクリック
2. 「Visual Basic」を押す

STEP

「標準モジュール」を挿入する

• 「挿入」タブを押す
• 「標準モジュール」をクリック

STEP

「プロシージャ」を挿入

• 「挿入」タブを押す
• 「プロシージャ」をクリック

STEP

マクロ名を設定する

• マクロ名を入力する
• 「OK」ボタンを押す

STEP

セルを黄色に塗るコードをコピペする

以下のコードをコピペしましょう。

Range("A1").Interior.Color = vbYellow

このコードは、「A1セルの背景Interiorの色Colorを黄色vbYellowにする」という意味です。

STEP

マクロを実行して結果を確認

• 「▶」ボタンを押す
• エクセルに戻り、A1セルが黄色に塗られていることを確認

このコードを基本に、あとは「どのセルを・どう変えるか」の組み合わせを変えるだけで様々な書式設定ができます。

NumberFormatLocalで表示形式を設定しよう

ここではNumberFormatLocalというコードを使って、セルの表示形式を指定する方法を解説します。

Dr.オフィス

表示形式とは、セルに入っている値を変えずに、見た目だけを整える機能だよ！

例えば「1500」を「1,500」と見せたり、「123」を「000123」と見せるなど、表示形式を整えるだけでリストがぐっと見やすくなるので、ぜひ、取り入れてみてください。

作例として、「社員証再発行リスト」の次の3つの表示形式を変更します。

数字の社員番号を文字列として扱う

数字を文字列として扱うコードは以下のとおりです。

Range("B3:B12").NumberFormatLocal = "@"

@は、入力された内容を文字列としてそのまま表示するときに使用します。

これを設定しておくことで、社員番号など先頭に0があるものを入力しても、0が消えなくなります。

Range("B3:B12").NumberFormatLocal = "000000"

申請日を区切りなしの「yyyymmdd」と表示する

日付を区切りなしで詰めて表示したいときは、次のようにコードを書きましょう。

Range("E3:E12").NumberFormatLocal = "yyyymmdd"

yyyyは西暦4ケタ、mmは月、ddは日を表します。

yyyymmddをyyyy/mm/ddやyyyy-mm-ddに変えれば、区切りを設定することも可能です。

Range("E3:E12").NumberFormatLocal = "ggge年mm月dd日"

費用に3ケタ区切りのカンマと「円」を付ける

数字に3ケタ区切りのカンマを挿入し、単位「円」を付けるコードは以下のとおりです。

Range("I3:I12").NumberFormatLocal = "#,##0円"

#,##0で3ケタごとにカンマを入れることができ、その後ろに「円」を加えることで、1500が1,500円と表示されるようになります。

セルに入っている値は数値の1500のままであり、見た目に「円」が付くだけですので、計算に使うことができて便利です。

3つの表示形式を一度に指定するサンプルコード

上記3つの表示形式を一度に指定するサンプルコードは以下のとおりです。

Public Sub 表示形式を変更()

    Range("B3:B12").NumberFormatLocal = "@"

    Range("E3:E12").NumberFormatLocal = "yyyymmdd"

    Range("I3:I12").NumberFormatLocal = "#,##0円"

End Sub

このコードを実行すると、以下のようにリストが変化します。

エクセルVBAでセルの背景色や網かけを設定する

VBAを使って、セルの背景色を変化させることができます。

背景色を変えるコードはInterior.Colorです。

例えば、見出しの行に色を付けるだけで、リストは一気に見やすくなります。

リストの見出し行である2行目に、背景色を付けてみましょう。

Public Sub 見出しに背景色を付ける()

    Range("A2:I2").Interior.Color = RGB(221, 235, 247)

End Sub

このVBAを実行すると、以下のように2行目のセルが水色になります。

モノクロ印刷が決まっているリストでは、背景色の代わりに網かけを使うと、モノクロでもはっきり区別できます。

Dr.オフィス

網かけは斜線やドットの模様のことだよ！

網かけを設定するコードはInterior.Patternです。

社員証再発行リストの欄外で試してみましょう。

Public Sub 網かけサンプル()

    Range("L3").Interior.Pattern = xlGray8

    Range("L5").Interior.Pattern = xlGray16

    Range("L7").Interior.Pattern = xlGray25
  
    Range("L9").Interior.Pattern = xlPatternLightUp

    Range("L11").Interior.Pattern = xlPatternLightDown

End Sub

上記のコードを実行した結果は、以下のとおりです。

これ以外の網かけの種類については、マイクロソフト社のWebサイトをご確認ください。

Color・RGB・ColorIndexを使い分けよう

エクセルVBAで色を指定する方法は、3種類あります。

それぞれの違いを整理すると次の通りです。

RGB(赤,緑,青)

RGBは、色を赤・緑・青の3つの光の強さで指定する書き方です。

それぞれ0～255の数字で指定でき、数字が大きいほどその色が強くなります。

Dr.オフィス

どの数字がどの色になるかは、エクセルの「塗りつぶしの色→その他の色」画面で確認できるよ！

色が細かく指定できて、実務で一番使いやすいRGBを基本使いし、他の2つについては「こういった指定方法がある」ということだけ覚えておきましょう。

エクセルVBAで背景色を消す方法

背景色を消したいときは、ColorIndexにxlNoneを指定します。

Dr.オフィス

xlNoneは、「塗りつぶしなし」ということだよ！

A1セルの背景色を消すコードは、以下のとおりです。

Public Sub 背景色を消す()

   Range("A1").Interior.ColorIndex = xlNone

End Sub

マクロを実行して、A1セルの背景色が消えることを確認しましょう。

フォントの種類や大きさ・色をVBAで設定する

前章ではセルの背景色を設定しましたが、セル内に書かれたフォント自体もVBAで設定することができます。

文字の見た目を整えるコードはFontです。

この後ろに、文字の色やサイズなど、変えたい内容を続けて指定します。

作例として、「社員証再発行リスト」のタイトルであるA1セルの文字を整えてみましょう。

Public Sub タイトルの文字を整える()

    With Range("A1").Font
        .Bold = True
        .Size = 14
        .Color = RGB(31, 78, 120)
        .Name = "メイリオ"
    End With

End Sub

同じセルに複数の設定をするときは、With～End Withを使って範囲の指定を1回にまとめることで書き間違いを減らせます。

それぞれ以下の設定をしています。

• .Bold = True：文字を太字にする
• .Size = 14：文字の大きさを14ポイントにする
• .Color = RGB(31, 78, 120)：文字色を紺色にする
• .Name = "メイリオ"：フォントの種類をメイリオにする

上記のコードを実行すると、このように一括で文字を整えることができます。

エクセルVBAで表の外周や内側に罫線を設定する

リストを囲む罫線もエクセルVBAで設定できます。

罫線を引くコードはBordersを使い、この後ろに引きたい罫線の内容を指定するだけです。

以下のコードは、A2からI12セルに対して、格子状の罫線を設定しています。

Public Sub リストに罫線を引く()

    With Range("A2:I12").Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With

End Sub

前章と同様に、With～End Withを使って範囲の指定を1回にまとめています。

応用として、実務でよく使われる「外周がやや太め」「内側の横線は破線」「内側の縦線は実線」の罫線を引くコードをご紹介します。

サンプルコードは以下のとおりです。

Public Sub 外周と内側で罫線を変える()

    With Range("A2:I12")
        .BorderAround LineStyle:=xlContinuous, Weight:=xlMedium '外周
        .Borders(xlInsideHorizontal).LineStyle = xlDot '内側の横線
        .Borders(xlInsideVertical).LineStyle = xlContinuous '内側の縦線
    End With

End Sub

.BorderAroundは、指定範囲の外周だけにまとめて罫線を引くコードです。

また、Bordersに続けてカッコ内に位置を指定すれば、線を引く場所を細かく選べます。

線を引く場所を指定する際によく使うコードは、以下のとおりです。

Range("A2:I12").Borders.LineStyle = xlLineStyleNone

VBAでセルの配置と結合・折り返し表示をするには

実務でリストを作るときによく使われるのが、タイトル行のセルを結合して中央に配置する書式です。

セルの結合と文字の中央揃えは、以下のコードで設定できます。

Public Sub タイトルを結合して中央に置く()

    With Range("A1:I1")
        .Merge
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With

End Sub

次の設定をしています。

• .Merge：セルを結合する
• .HorizontalAlignment = xlCenter：文字の横方向を中央揃えにする
• .VerticalAlignment = xlCenter：文字の縦方向を中央揃えにする

なお、.HorizontalAlignmentと.VerticalAlignmentは、中央揃え以外にも設定できます。

主なコードは以下のとおりです。

Range("A1:I1").UnMerge

Range("A1:I1").HorizontalAlignment = xlCenterAcrossSelection

エクセルでは、セルの幅よりも長い文字を表示させるために、文字を折り返したり、文字を自動的に縮小して1行に収めたりすることがあります。

これらをVBAで行うときは以下のコードを使います。

Range("F3:F12").WrapText = True '折り返して表示

Range("F3:F12").ShrinkToFit = True '文字を縮小して1行に収める

なお、折り返して表示する.WrapTextと、文字を縮小する.ShrinkToFitは、両立することができません。

片方をTrueにすると、自動的にもう片方は無効になるので注意が必要です。

条件付き書式を設定するエクセルVBA

この章では、セルの値に応じて自動で書式が変わる「条件付き書式」を設定します。

エクセルの条件付き書式を、マクロで設定することが可能です。

一度設定してしまえば値を入力するだけで書式が切り替わるので、手作業でいちいち塗りなおす必要がなくなります。

例えば、以下のG列「発行状況」に、セルの色が切り替わる条件付き書式を設定してみましょう。

サンプルコードは以下のとおりです。

Public Sub 発行状況で色を変える()

    Dim rng As Range
    Set rng = Range("G3:G12")

    rng.FormatConditions.Delete '既存の条件付き書式を消しておく

    '「未対応」を赤にする
    With rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$G3=""未対応""")
        .Interior.Color = RGB(255, 199, 206)
    End With

    '「対応中」を黄にする
    With rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$G3=""対応中""")
        .Interior.Color = RGB(255, 235, 156)
    End With

    '「完了」を緑にする
    With rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$G3=""完了""")
        .Interior.Color = RGB(198, 239, 206)
    End With

End Sub

条件付き書式を追加するコードはFormatConditions.Addです。

Addに続くカッコ内に、書式が切り替わる条件を設定します。

Type:=xlExpressionは、書式が切り替わる条件を数式で指定することを示しています。

Formula1:="=$G3=""未対応"""は、「G列の値が『未対応』なら」という条件です。

Formula1はエクセルVBAで条件付き書式で条件を指定するときなどに使われます。

変数rngに対象範囲であるRange("G3:G12")を格納しています。
同じ範囲を何回も書かずに済み、リストの範囲が変わってもコードの冒頭を修正するのみで済むので、書き方を覚えておくと便利です。

また、条件付き書式は、今日の日付を取得するTODAY()と組み合わせることで期限管理に使えます。

社員証の有効期限が30日以内に迫ったセルや、期限が過ぎたセルを、自動でオレンジ色に塗るコードをご紹介します。

Public Sub 期限間近を警告する()

    Dim rng As Range
    Set rng = Range("H3:H12")

    rng.FormatConditions.Delete

    '今日から30日以内なら色を付ける
    With rng.FormatConditions.Add(Type:=xlExpression,Formula1:="=AND($H3<>"""",$H3<=TODAY()+30)")
        .Interior.Color = RGB(255, 217, 102)
    End With

End Sub

TODAY()は常に今日の日付を指すので、リストを開くたびに期限が近付いたセルが自動でオレンジ色になります。

"=AND($H3<>"""",$H3<=TODAY()+30)"は複雑に見えますが、「H列が空欄ではなく、なおかつ有効期限が今日から30日以内」という条件を指定しています。

セル番地やRGBの色をカスタマイズしてご活用ください。

エクセルVBAの書式設定に関するQ＆A

VBAで書式が反映されない場合はどうすればいいですか？

まずは以下3点を確認しましょう。

1. セル範囲の指定が正しいか
2. コードの綴りやドットに間違いがないか、全角になっていないか
3. NumberFormatLocalで日付や数値の表示形式を設定する場合、そもそものセルの値が「文字列」になっていないか
   なっていたら値を数値や日付に直してから設定し直す

それでも直らない場合は、以下のコードで書式をクリアしてから設定しなおしましょう。

Range("A1:I12").ClearFormats

エクセルVBAでyyyymmddの書式設定をする方法はどうやりますか？

.NumberFormatLocalに"yyyymmdd"を指定すれば設定できます。

詳しくはNumberFormatLocalで表示形式を設定しようをご覧ください。

エクセルVBAで書式を一括設定しよう！

エクセルVBAで表示形式や背景色、罫線などの書式設定をする方法を解説しました。

書式設定で一番大切なのは、「どのセルを・どう変えるか」という考え方です。

この型さえ理解すれば、あとは変えたい内容に応じてコードを差し替えるだけです。

それでは書式設定の主なコードをおさらいしましょう。

上記のコードに加えて、FormatConditionsで条件付き書式を設定することで、入力内容に応じて書式を自動で変えられます。

まずは実務に使えそうなコードを1行から試して、VBAで書式設定を自動化する便利さを実感してみてください！

今回の書式設定のほか、エクセルVBAの便利ワザを知りたい方は、以下をご参照いただけたら幸いです。