VBA入門
条件付き書式(FormatCondition)

ExcelマクロVBAの基本と応用、エクセルVBAの初級・初心者向け解説
公開日:2013年5月以前 最終更新日:2021-10-30

第91回.条件付き書式(FormatCondition)


マクロ VBA 条件付き書式

条件付き書式は、シート上で設定しておいた方が良いのですが、
事前に設定しておけない場合は、マクロVBAで条件付き書式を設定します。


VBAで条件付き書式を設定する場合は、
セル(Rangeオブジェクト)のFormatConditionsコレクションにFormatConditionオブジェクトを追加することで行います。

少々イメージしづらいと思います。
具体的な手順は、以下になります。

・セル(Rangeオブジェクト)のFormatConditionsコレクションのAddメソッドでFormatConditionオブジェクトを追加
・追加されたFormatConditionオブジェクトに対して書式を設定

理解しづらい部分はあると思いますが、
まずは、
FormatConditionsコレクション
FormatConditionオブジェクト
これの構文について詳細を見てみましょう。


FormatConditionsコレクション

1 つのセル範囲の条件付き書式のコレクションを表します。
各書式は、コレクションの要素、FormatConditionオブジェクトになります。

FormatConditionsコレクションのプロパティとメソッド

メソッド Add 新しい条件付き書式を追加します。
追加されたFormatConditionオブジェクトが返されます。
AddAboveAverage 指定された範囲の条件付き書式ルールを示す新しい AboveAverage オブジェクトを返します。
AddColorScale セルの色でグラデーションを使用して、選択した範囲内に含まれるセルの値の相対的な違いを示す条件付き書式ルールを表す新しい ColorScale オブジェクトを返します。
AddDatabar 指定された範囲のデータ バーの条件付き書式ルールを表す Databar オブジェクトを取得します。
AddIconSetCondition 指定された範囲のアイコン セットの条件付き書式ルールを示す新しい IconSetCondition オブジェクトを返します。
AddTop10 指定された範囲の条件付き書式ルールを示す Top10 オブジェクトを取得します。
AddUniqueValues 指定されたセル範囲の条件付き書式ルーを表す新しい UniqueValues オブジェクトを返します。
Delete オブジェクトを削除します。
Item コレクションから単一のオブジェクトを返します。
プロパティ Application 対象となるオブジェクトが指定されない場合は、Excel アプリケーション (Application オブジェクト) を返します。
対象となるオブジェクトが指定された場合は、指定されたオブジェクトを作成した Application オブジェクトを返します。
OLE オートメーションを使っていて、オブジェクトのアプリケーションにアクセスするときなどに、このプロパティを使います。
値の取得のみ可能です。
Count コレクションに含まれるオブジェクトの数を表す長整数型 (Long) の値を返します。
Creator 現在のオブジェクトが作成されたアプリケーションを示す 32 ビットの整数を取得します。
値の取得のみ可能です。長整数型 (Long) の値を使用します。
Parent 指定されたオブジェクトの親オブジェクトを取得します。
値の取得のみ可能です。
※2007以降のみで使用可能なメソッドプロパティを含んでいます。

必須となるAddメソッドを説明します。

Addメソッド

式.Add(Type, Operator, Formula1, Formula2)

戻り値はFormatConditionオブジェクトです。

Type

セル値またはオブジェクト式のどちらを基に条件付き書式を設定するかを指定します。

xlAboveAverageCondition 平均以上の条件
xlBlanksCondition 空白の条件
xlCellValue セルの値
xlColorScale カラー スケール
xlDatabar データバー
xlErrorsCondition エラー条件
xlExpression 演算
XlIconSet アイコン セット
xlNoBlanksCondition 空白の条件なし
xlNoErrorsCondition エラー条件なし
xlTextString テキスト文字列
xlTimePeriod 期間
xlTop10 上位の 10 の値
xlUniqueValues 一意の値
Operator 条件付き書式の演算子を指定します。指定できる定数は、XlFormatConditionOperator クラスの xlBetween、xlEqual、xlGreater、xlGreaterEqual、xlLess、xlLessEqual、xlNotBetween、xlNotEqual のいずれかです。
Type が xlExpression の場合、引数 Operator は無視されます。
Formula1 条件付き書式に関連させる値またはオブジェクト式を指定します。
定数値、文字列値、セル参照、または数式を指定できます。
Formula2 引数 Operator に xlBetween または xlNotBetween を指定した場合、条件付き書式の 2 番目の部分に
関連させる値またはオブジェクト式を指定します。
それ以外を指定した場合、この引数は無視されます。
定数値、文字列値、セル参照、または数式を指定できます。

FormatConditionsのメソッドの使用例

Range.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, Formula1:="50"

「セルの値」「以下」「50」になります。
書式を設定するのは、FormatConditionオブジェクトです。


FormatConditionオブジェクト

FormatConditionオブジェクトのプロパティとメソッド

メソッド Delete オブジェクトを削除します。
Modify 既存の条件付き書式を変更します。
ModifyAppliesToRange 書式のルールが適用されるセル範囲を設定します。
SetFirstPriority 条件付き書式ルールの優先度の値を "1" に設定し、ワークシート内の他のルールより先に評価されるようにします。
SetLastPriority 条件付き書式ルールの順位を設定し、ワークシート上の他のすべてのルールの後に評価されるようにします。
プロパティ Application 対象となるオブジェクトが指定されない場合は、Excel アプリケーション (Application オブジェクト) を返します。対象となるオブジェクトが指定された場合は、指定されたオブジェクトを作成した Application オブジェクトを返します。
OLE オートメーションを使っていて、オブジェクトのアプリケーションにアクセスするときなどに、このプロパティを使います。
値の取得のみ可能です。
AppliesTo 書式ルールを適用するセル範囲を表す Range オブジェクトを返します。
Borders スタイルまたはセル範囲 (条件付き書式の一部として定義された範囲を含む) の罫線を表す Borders コレクションを取得します。
Creator 現在のオブジェクトが作成されたアプリケーションを示す 32 ビットの整数を取得します。
値の取得のみ可能です。長整数型 (Long) の値を使用します。
DateOperator 書式の条件に使用される日付演算子を指定します。
値の取得および設定が可能です。
Font 指定したオブジェクトのフォントを表す Font オブジェクトを取得します。
Formula1 条件付き書式または入力規則に使用されている値またはオブジェクト式を返します。定数値、文字列値、セル参照、または数式を使用できます。値の取得のみ可能です。
文字列型 (String) の値を使用します。
Formula2 条件付き書式または入力規則の 2 番目の部分に使用されている値またはオブジェクト式を返します。Operator プロパティに xlBetween または xlNotBetween が設定されている場合にだけ、このプロパティを使用できます。
定数値、文字列値、セル参照、または数式を指定できます。
値の取得のみ可能です。文字列型 (String) の値を使用します。
Interior 指定されたオブジェクトの塗りつぶし属性を表す Interior オブジェクトを取得します。
NumberFormat 条件付き書式ルールが True に評価された場合にセルに適用される表示形式を設定します。
値の取得および設定が可能です。バリアント型 (Variant) の値を使用します。
Operator 条件付き書式の演算子を表す長整数型 (Long) の値を返します。
Parent 指定されたオブジェクトの親オブジェクトを取得します。
値の取得のみ可能です。
Priority 条件付き書式ルールの優先度の値を取得、または設定します。
優先度は、ワークシート内に複数の条件付き書式ルールが存在する場合、その評価の順序を決定します。
PTCondition 条件付き書式がピボットテーブル チャートに適用されるかどうかを表すブール型 (Boolean) の値を返します。値の取得のみ可能です。
ScopeType 条件付き書式がピボットテーブル チャートに適用される場合、その適用範囲を表す XlPivotConditionScope クラスの定数を取得、または設定します。
StopIfTrue 現在のルールが True に評価された場合、そのセルの書式ルールをさらに評価するかどうかを表すブール型 (Boolean) の値を取得、または設定します。
Text 条件付き書式ルールで使用されるテキスト文字列を指定する文字列型 (String) の値を設定します。
TextOperator 条件付き書式ルールで実行されるテキスト検索を指定する XlContainsOperator クラスの定数の 1 つを設定します。
Type オブジェクトの種類を表す長整数型 (Long) の値を返します。
xlFormatConditionType クラスの定数を使用します。
※2007以降のみで使用可能なメソッドプロパティを含んでいます。

良く質問されるのが、

StopIfTrue

これが何かという事です。
これは、シートの条件付書式の「ルールの管理」のダイアログ

マクロ VBA 条件付き書式

この「条件を満たす場合は停止」になります。
Trueで停止です。


FormatConditionオブジェクトに含まれる、下位のオブジェクト

オブジェクト プロパティ
Font Bold
Color
ColorIndex
FontStyle
Italic
Strikethrough
Underline
下線スタイル (会計) は使用できません。
Border Bottom
Color
Left
Right
Style
使用できる罫線の種類は、xlNone、xlSolid、xlDash、xlDot、xlDashDot、xlDashDotDot、xlGray50、xlGray75、xlGray25 です。それ以外はサポートされていません。
Top
Weight
使用できる罫線の太さは、xlWeightHairline および xlWeightThin です。それ以外はサポートされていません。
Interior Color
ColorIndex
Pattern
PatternColorIndex

FormatConditionのプロパティ設定の使用例

Range.FormatConditions(1).Font.ColorIndex = 3

(1)は、先頭の条件になります。


条件付き書式のマクロVBA実践例

"A1:A10"セルに対して、
90%未満の場合は赤色で塗りつぶす
100%未満の場合は黄色で塗りつぶす

これを行うVBAコードです。

Sub sample1()
  With Worksheets("Sheet1").Range("A1:A10")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="90%"
    .FormatConditions(1).Interior.Color = vbRed
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="100%"
    .FormatConditions(2).Interior.Color = vbYellow
  End With
End Sub

セルに既に条件付き書式が設定されている場合を想定して、条件付き書式を一旦削除しています。
最初のFormatConditions.AddでFormatConditions(1)が追加され、
次のFormatConditions.AddでFormatConditions(2)が追加されます。

2個くらいなら、FormatConditionsの要素位置を数値で指定しても問題ないでしょうが、
もっと多くなってくると、数値指定が面倒になってきます。
以下のように、Addメソッドの戻り値のFormatConditionオブジェクトを変数に入れて使うことが出来ます。

Sub sample2()
  Dim fc As FormatCondition
  With Worksheets("Sheet1").Range("A1:A10")
    .FormatConditions.Delete
    Set fc = .FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="90%")
    fc.Interior.Color = vbRed
    Set fc = .FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="100%")
    fc.Interior.Color = vbYellow
  End With
End Sub

書き方の注意点.
FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="90%"

この場合は、Addの後ろに半角空白をおいて引数を指定します。

Set fc = .FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="90%")

この場合は、Addの後ろの()の中に引数を指定します。
VBAにおける括弧()の使い方
・基本文型 ・VBAにおける括弧() ・VBAにおける半角空白の意味 ・戻り値を他の用途に使う時 ・括弧()の使い方の基本文型 ・Callを省略しなければ全て括弧が必要になる ・最後に一言


マクロVBAの条件付き書式について

FormatConditionsコレクション、FormatConditionsオブジェクト、さらに下位のオブジェクトがあり、
それぞれに、プロパティ・メソッドを持っています。
これらを全て覚える事は大変ですし、あまり意味がありません。
適宜、マクロの記録を利用して、各プロパティ・メソッドを調べて下さい。

以下も参考にして下さい。
「マクロの記録で覚えるVBA」の第21回.条件付き書式
非常に便利で、ぜひ使いこなしたい機能の1つに、条件付き書式があります。今回は、この条件付き書式をマクロの記録をしてみましよう。では、マクロの記録ですが、バージョンによって操作が違うので、まずは、2007、2010です。
増殖した条件付き書式を整理統合する
・条件付き書式の増殖に関する、Microsoft サポート ・増殖した条件付き書式の実例と対応 ・簡単なVBAでの対応 ・VBAで条件付き書式を整理統合した結果 ・今回のVBAコードの発想について ・Application.ConvertFormulaメソッド ・増殖した条件付き書式を整理統合するVBA ・条件付き書式で設定できる書式 ・増殖した条件付き書式を整理統合するVBAの使い方 ・増殖した条件付き書式を整理統合の最後
条件付き書式で変更された書式を取得する
条件付き書式が設定されている場合、当然ですが見た目は、本来そのセルに設定されている書式ではなく、条件付き書式の条件によって設定されている書式になります。VBAで、この条件付き書式によって設定された書式を取得します。これが取得できるようになったのは、Excel2010からですので、このページで紹介するVBAコードはE…
VBA+SeleniumBasicで検索順位チェッカー(改) ・・・ アイコンセットを使っています
・シート構成 ・検索順位チェッカーのVBA全コード ・最後に
オートフィルタを退避回復するVBAクラス ・・・ 条件付き書式のアイコンセットを取得しています
・オートフィルターに関するページ ・オートフィルターを退避回復するVBAクラスの概要 ・オートフィルターを退避し回復するクラスのVBAコード ・クラスを利用する標準モジュールのVBAコード ・処理の要点と解説 ・最後に




同じテーマ「マクロVBA入門」の記事

第88回.並べ替え(Sort)
第89回.オートフィルター(AutoFilter)
第90回.フィルターオプションの設定(AdvancedFilter)
第91回.条件付き書式(FormatCondition)
第126回.入力規則(Validation)
第92回.名前定義(Names)
第93回.ピボットテーブル(PivotTable)
第94回.コメント(Comment)
第95回.ハイパーリンク(Hyperlink)
第96回.グラフ(Chart)
第97回.図形オートシェイプ(Shape)


新着記事NEW ・・・新着記事一覧を見る

ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ