EXCEL VBA セルの条件付き書式の設定・（書式・文字色・背景色・入力条件により処理操作・条件に合うセル）
EXCEL VBA セルの条件付き書式の設定（書式・文字色・背景色・入力条件により処理操作・条件に合うセル・条件を満たす）
今回説明するのは、EXCEL VBAで条件付き書式設定の設定方法を説明致します。EXCEL2007から条件付き書式の機能が拡張され、セルに入力した条件により、背景色・文字色・罫線などを設定する事とデータバー・カラースケール・アイコンセットも設定する事が出来て、視覚的な表現を表す事が出来る様になりました。条件書式の便利なところは、一度設定するとそのセルの数値・文字等が変更されれば、条件付き書式を設定したセル位置に背景色・文字色等を自動的に変更する事ができるので、とても便利です。VBA/マクロ等でプログラムを数値・文字等が変更される度に実行する必要が無いので、取り扱いにも便利です。それでは、条件付きの書式設定（FomatConditionsコレクション）の利用方法をサンプルプログラムを交えて説明致します。
●【FormatConditions オブジェクト (Excel)、下記を参照して下さい】（Microsoft社 様）】
https://docs.microsoft.com/ja-jp/office/vba/api/excel.formatconditions
●【ModifyAppliesToRange メソッド (Excel)、下記を参照して下さい】（Microsoft社 様）】
https://docs.microsoft.com/ja-jp/office/vba/api/excel.top10.modifyappliestorange
● FormatConditionsコレクションを利用するには、下記の通りに設定を行います。
【新規ルール】・・・・FomatConditionsコレクションAddメゾットで条件付き書式の設定をします。
【ルールの編集】・・・FomatConditionsコレクションModifyメゾットで条件付き書式の編集をします。
【ルールの削除】・・・FomatConditionsコレクションDeleteメゾットで条件付き書式の削除をします。
●【FomatConditionsコレクションAddメゾット】
● オブジェクト.Add(type[,Operator,Formula1,Formula2])
●Type・・・条件付き書式の種類をXlFormatConditionTypeクラスの定数を設定します。（下記参照）
| 定数 | 値 | 内容 |
|---|---|---|
| xlCellValue | 1 | セルの値 |
| xlExpression | 2 | 数式（演算） |
| xlColorScale | 3 | カラースケール |
| xlDatabar | 4 | データーバー |
| xlTop10 | 5 | 上位10の値 |
| XlIconSet | 6 | アイコンセット |
| xlUniqueValues | 8 | 一意の値 |
| xlTextString | 9 | テキスト文字列 |
| xlBlanksCondition | 10 | 空白の条件 |
| xlTimePeriod | 11 | 期間 |
| xlAboveAverageCondition | 12 | 平均以上の条件 |
| xlNoBlanksCondition | 13 | 空白の条件なし |
| xlErrorsCondition | 16 | エラー条件 |
| xlNoErrorsCondition | 17 | エラー条件なし |
| xlCompareColumns | 18 | 列の比較 |
●Operator・・・条件付き書式の演算子をXlFormatConditionOpertorクラスの定数で設定します。※省略可能 （下記参照）
| 定数 | 値 | 内容 |
|---|---|---|
| xlBetween | 1 | 範囲内（次の値の間） |
| xlNotBetween | 2 | 範囲外（次の値の間以外） |
| xlEqual | 3 | 次の値に等しい |
| xlNotEqual | 4 | 次の値に等しくない |
| xlGreater | 5 | 次の値より大きい |
| xlLess | 6 | 次の値より小さい |
| xlGreaterEqual | 7 | 次の値以上 |
| xlLessEqual | 8 | 次の値以下 |
●Fomula1・・・条件となる値を設定します。セル参照・文字列・数値・数値を使って設定できます。※省略可能
●Fomula2・・・引数のOperatorがxlBetweenまたは、xlNotBetweenの時に2つ目の条件となる値を設定します。※省略可能
【使用例①】：B列の得点に応じて60点以上の場合は背景色を黄緑にします。
※下記のプログラムを実行すると下記の通り条件付き書式に設定されセルに反映されます。
|
1 2 3 4 5 6 7 8 |
' ' Sub FormatConditions_Add01() '60点以上の場合は、背景色を黄緑にする。 Range("B2:B10").FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=60).Interior.ColorIndex = 4 End Sub ' |
【使用例②】：B列の年齢に応じて20才以上の場合は背景色を黄緑にします。20才未満の場合は背景色を赤色にします。（2つの条件書式の設定）
※下記のプログラムを実行すると下記の通り条件付き書式に設定されセルに反映されます。
|
1 2 3 4 5 6 7 8 9 10 |
' ' Sub FormatConditions_Add02() '20才以上の場合は、背景色を黄緑にする。20才未満の場合は、背景色を赤にする。 With Range("B2:B10") .FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=20).Interior.ColorIndex = 4 .FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:=20).Interior.ColorIndex = 3 End With End Sub ' |
【使用例③】：B列の金額に応じて10,000～15,000の間は、背景色を黄緑にします。（2つの間の設定）
※下記のプログラムを実行すると下記の通り条件付き書式に設定されセルに反映されます。
|
1 2 3 4 5 6 7 8 |
' ' Sub FormatConditions_Add03() '金額が10000～15000の場合は、背景色を黄緑にする。 Range("B2:B10").FormatConditions.Add(Type:=xlCellValue, Operator:=xlBetween, Formula1:=10000, Formula2:=15000).Interior.ColorIndex = 4 End Sub ' |
【注意点】
● FormatConditions.Addメゾットをプログラムを実行する場合で注意点があります。条件付き書式の設定を削除しないで、何度もFormatConditions.Addメゾットを実行すると下記の通り同じ条件書式の設定が複数登録されてしまいます。これを回避する場合は、削除してから登録するなどの方法を考える必要があります。
※条件付き書式の設定の削除例（セルB2～B10）に設定された条件書式の設定が削除されます。
|
1 2 3 4 5 6 7 8 9 |
' ' Sub FormatConditions_del00() 'FormatConditionsDeleteメゾットの使用例 Range("B2:B10").FormatConditions.Delete End Sub ' |
EXCEL VBA セルの条件付き書式の設定（追加・設定方法 Addメゾット）
下記のサンプルプログラムは、条件付き書式の設定を追加するプログラムです。ワークシートのB列にデータ件数に応じて追加します。データ件数の変更にも対応するために、B列に設定している条件付き書式を削除して、条件付き書式を追加設定を行います。それでは、サンプルプログラムを交えて順番に説明いたします。
【プログラムの流れ】
① ワークシート上のB列の最終行を取得します。
② これから条件付き書式設定を行うセルに対して、条件付き書式が設定されているか調べます（０を超える場合は設定済み）
③ 条件付き書式が設定されている場合は、設定カウント数が１以上となるので、設定する前に、設定範囲の条件付き書式設定を削除します。
④ 1件目の条件付き書式を設定します。設定範囲のB列の金額が10000以上の場合は、背景色が水色に塗りつぶします。
⑤ 2件目の条件付き書式を設定します。設定範囲のB列の金額が10000未満の場合は、背景色がオレンジ色に塗りつぶします。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
' ' Sub 条件書式の設定01() Dim lRow As Long lRow = Cells(Rows.Count, "B").End(xlUp).Row 'B列の最終行を取得 If Range("B2:B" & lRow).FormatConditions.Count > 0 Then '条件付き書式が設定されているか調べます（０を超える場合は設定されている） Range("B2:B" & lRow).FormatConditions.Delete '設定されている条件付き書式を削除します。 End If With Range("B2:B" & lRow) .FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=10000).Interior.ColorIndex = 33 'B列の金額が10000以上の場合は、背景色が水色に塗りつぶす。 .FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:=10000).Interior.ColorIndex = 46 'B列の金額が10000未満の場合は、背景色がオレンジ色に塗りつぶす。 End With End Sub ' |
（画面クリックして拡大）
EXCEL VBA セルの条件付き書式の設定（セルに登録されている条件で条件付き書式を設定：繰り返し設定も可能）
下記のサンプルプログラムは、セルに登録されている条件付き書式設定（値・背景色・条件）通りに、条件付き書式設定として設定するサンプルプログラムです。何度も繰り返しても設定出来る様になっていますので、条件付き書式設定を頻繁に変更する際は、とても便利だと思います。それでは、サンプルプログラムを交えて順番に説明いたします。
●Operator・・・条件付き書式の演算子をXlFormatConditionOpertorクラスの定数で設定します。※省略可能 （下記参照）
| 定数 | 値 | 内容 |
|---|---|---|
| xlBetween | 1 | 範囲内（次の値の間） |
| xlNotBetween | 2 | 範囲外（次の値の間以外） |
| xlEqual | 3 | 次の値に等しい |
| xlNotEqual | 4 | 次の値に等しくない |
| xlGreater | 5 | 次の値より大きい |
| xlLess | 6 | 次の値より小さい |
| xlGreaterEqual | 7 | 次の値以上 |
| xlLessEqual | 8 | 次の値以下 |
【条件付き書式設定】セルG列～I列
① セル範囲（B2～C12）内のセルの値が2,500,000以上の値のセルに背景色（40）で塗りつぶします。
② セル範囲（B2～C12）内のセルの値が１,500,000と等しい値のセルに背景色（39）で塗りつぶします。
③ セル範囲（B2～C12）内のセルの値が500,000以下の値のセルに背景色（38）で塗りつぶします。
【プログラムの流れ】
① ワークシート上の条件付き書式設定を全てクリアーします。
② B列の最終行を取得します。（条件付き書式設定が設定する部分の範囲取得）
③ G列の最終行を取得します。（条件付き書式設定を設定する参照データの範囲取得）
④ G列に設定されている条件付き書式設定の件数分を繰り返します。
⑤ 条件付き書式設定の値・背景色・条件をセルに登録されているデータより取得します。
⑥ 取得した⑤データにより、条件付き書式設定を行います。（追加登録）
⑦ G列に登録されている件数分の条件付き書式設定を行います。④へ
★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
● conditional_formatting _sumple
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
' ' Sub 条件書式の設定変更02() 'セルに設定条件を参照して条件付き書式を再設定します。 Dim I, L, lRow, mRow As Long Dim Val, CC As Long Dim Ope As Variant Cells.FormatConditions.Delete 'ワークシート上の条件付き書式設定をクリアーします。 lRow = Cells(Rows.Count, "C").End(xlUp).Row 'B列の最終行を取得(データ) mRow = Cells(Rows.Count, "G").End(xlUp).Row 'G列の最終行を取得（設定値） For L = 2 To mRow 'G列の設定値を順番に繰り返します。 Val = Range("G" & L) 'G列の設定値（値）を代入します。 CC = Range("H" & L) 'H列の設定値（背景色）を代入します。 Ope = Range("I" & L) 'I列の設定値（条件）を代入します。 Range("B2:C" & lRow).FormatConditions.Add(Type:=xlCellValue, Operator:=Ope, Formula1:=Val).Interior.ColorIndex = CC '条件付き書式の設定（追加）を行います。 Next L End Sub ' |
（画面クリックして拡大）
EXCEL VBA セルの条件付き書式の設定（変更・修正・訂正）FomatConditionsコレクションModifyメゾット
FomatConditionsコレクションModifyメゾットを使う事で条件付き書式の編集・修正・訂正を行う事が出来ます。
下記のサンプルプログラムでは、条件付き書式の設定が既に設定されている設定内容を変更又は修正を行う事ができます。それでは、サンプルプログラムを交えて順番に説明いたします。
【ルールの編集】・・・FomatConditionsコレクションModifyメゾットで条件付き書式の編集を行う事が出来ます。
●【FomatConditionsコレクションModifyメゾット】
● オブジェクト.Modify(type[,Operator,Formula1,Formula2])
●Type・・・条件付き書式の種類をXlFormatConditionTypeクラスの定数を設定します。（下記参照）
| 定数 | 値 | 内容 |
|---|---|---|
| xlCellValue | 1 | セルの値 |
| xlExpression | 2 | 数式（演算） |
| xlColorScale | 3 | カラースケール |
| xlDatabar | 4 | データーバー |
| xlTop10 | 5 | 上位10の値 |
| XlIconSet | 6 | アイコンセット |
| xlUniqueValues | 8 | 一意の値 |
| xlTextString | 9 | テキスト文字列 |
| xlBlanksCondition | 10 | 空白の条件 |
| xlTimePeriod | 11 | 期間 |
| xlAboveAverageCondition | 12 | 平均以上の条件 |
| xlNoBlanksCondition | 13 | 空白の条件なし |
| xlErrorsCondition | 16 | エラー条件 |
| xlNoErrorsCondition | 17 | エラー条件なし |
| xlCompareColumns | 18 | 列の比較 |
●Operator・・・条件付き書式の演算子をXlFormatConditionOpertorクラスの定数で設定します。※省略可能 （下記参照）
| 定数 | 値 | 内容 |
|---|---|---|
| xlBetween | 1 | 範囲内（次の値の間） |
| xlNotBetween | 2 | 範囲外（次の値の間以外） |
| xlEqual | 3 | 次の値に等しい |
| xlNotEqual | 4 | 次の値に等しくない |
| xlGreater | 5 | 次の値より大きい |
| xlLess | 6 | 次の値より小さい |
| xlGreaterEqual | 7 | 次の値以上 |
| xlLessEqual | 8 | 次の値以下 |
●Fomula1・・・条件となる値を設定します。セル参照・文字列・数値・数値を使って設定できます。※省略可能
●Fomula2・・・引数のOperatorがxlBetweenまたは、xlNotBetweenの時に2つ目の条件となる値を設定します。※省略可能
●条件付き書式の設定変更については、FomatConditionsコレクションModifyメゾットで条件付き書式を編集・訂正・修正などを行う事が出来ますが、同じセルに複数の条件付き書式の設定がされている場合は、上図の左側のFormatConditions(番号)を指定する事で、条件付き書式を編集する事が出来ます。
【使用例①】
●条件付き書式設定を設定して、設定した条件付き書式設定を変更します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
' ' Sub 条件書式の設定変更01() '既に設定されている条件付き書式を変更します。 Cells.FormatConditions.Delete 'ワークシート上の条件付き書式をクリアーします。 '設定 Range("B2:D12").FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=25000).Interior.ColorIndex = 33 'セル範囲（B2～D12）のセルの値が25,000以上の場合は、背景色を水色に塗りつぶします。 MsgBox "条件書式設定変更します" '変更 Range("B2:D12").FormatConditions(1).Modify Type:=xlCellValue, Operator:=xlBetween, Formula1:=5000, Formula2:=15000 'セル範囲（B2～D12）のセルの値が5000～15000の範囲内は、背景色を塗りつぶします。 End Sub ' |
EXCEL VBA セルの条件付き書式の設定（条件付き書式の設定範囲を再設定）ModifyAppliesToRangeメゾット
下記のサンプルプログラムは、条件付き書式設定で設定したセル範囲を再設定するModifyAppliesToRangeメゾットの利用方法を説明いたします。行や列を追加・削除して設定範囲を再設定する時に使用します。それでは、サンプルプログラムを交えて順番に説明いたします。
●【FomatConditionsコレクションModifyAppliesToRangeメゾット】
● オブジェクト.ModifyAppliesToRange(Range)
【処理の流れ】
【条件付き書式のセル範囲】
【プログラムの流れ】
① A列の最終行を取得します。（データの最終行）
② 条件付き書式の設定数を把握します。（設定数）
③ 条件付き書式の設定分、繰り返します。
④ 条件付き書式のセル範囲を再設定します。
⑤ ③へ繰り返します。設定分全て終わるまで
【プログラム実行条件】
・事前に条件付き書式の設定を行う。
・データ範囲は、A列～D列まで登録する。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
' ' ' Sub 条件付き書式の再設定01() '条件付き書式の範囲を再設定します。 Dim I, L, lRow As Long lRow = Cells(Rows.Count, "A").End(xlUp).Row 'A列の最終行を取得(データ) I = Range("B2").FormatConditions.Count '条件付き書式の設定数を把握します。 For L = 1 To I '条件付き書式の設定数分繰り返します。 Range("B2:D2").FormatConditions(L).ModifyAppliesToRange Range("B2:D" & lRow) '条件付き書式のセル範囲を再設定します。 Next L End Sub ' |
（画面クリックして拡大）
EXCEL VBA セルの条件付き書式の削除 シート全体・指定削除・部分削除 （FormatCondition.Delete）
下記のサンプルプログラムは、セルの条件付き書式を削除（解除）する方法を説明します。条件付き書式を削除には、FormatCondition.Deleteメゾットを利用します。それでは、順番に説明いたします。
●【FormatCondition.Deleteメゾット】
● オブジェクト.FormatCondition.Delete
① 条件付き書式の設定の削除例（セルB2～B10）に設定された条件付き書式設定が削除されます。
|
1 2 3 4 5 6 7 8 9 |
' ' Sub FormatConditions_del00() 'FormatConditionsDeleteメゾットの使用例 Range("B2:B10").FormatConditions.Delete End Sub ' |
② 条件付き書式の設定の削除例、指定した条件付き書式設定のみ削除 （2番目の条件付き書式を削除）
|
1 2 3 4 5 6 7 8 |
' ' Sub FormatConditions_de02() 'FormatConditionsDeleteメゾットの使用例（2番目の条件付き書式を削除） Range("B2:D12").FormatConditions(2).Delete End Sub ' |
③ 条件付き書式の設定の削除例、ワークシート上の全ての条件付き書式が削除
|
1 2 3 4 5 6 7 |
' ' Sub FormatConditions_del03() 'FormatConditionsDeleteメゾットの使用例(シート上の全ての条件付き書式の設定をクリアー) Cells.FormatConditions.Delete End Sub ' |
EXCEL VBA セルの条件付き書式の削除 ブック内の全体の条件付き書式を削除 （FormatCondition.Delete）
下記のサンプルプログラムは、セルの条件付き書式を削除するサンプルプログラムです。ブック内の全体の条件付き書式設定を全て削除します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
' ' Sub FormatConditions_del04() 'FormatConditionsDeleteメゾットの使用例(ブック全体の条件付き全てを削除します。 Dim Ws As Worksheet For Each Ws In Worksheets 'ブック内のワークシート全てを繰り返します。 Ws.Cells.FormatConditions.Delete 'ワークシート上の全ての条件付き書式を削除します。 Next Ws '最後のシートまで繰り返します。 End Sub ' |
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。