VBA セルに条件付き書式を設定する (Range.FormatConditions)

はじめに

Excel VBA マクロでセルに条件付き書式を設定や削除する方法を紹介します。

Range("B1").FormatConditions または Cells(1, 2).FormatConditions プロパティから、セル「B1」の条件付き書式を設定できます。

FormatCondition オブジェクトから、通常の条件付き書式に色を付けるなど書式を設定できます。他にもカラースケール ColorScale や、データバー Databar などもあります。

FormatConditions.Delete メソッドから、条件付き書式を削除できます。

条件付き書式はとても複雑です。どのように設定するかは Excel の「マクロの記録」から操作して確認するのが一番簡単です。
条件付き書式を取得するには「セルの条件付き書式を取得する」をご覧ください。
セルを範囲指定するすべての方法は「セルを範囲指定して取得する」をご覧ください。
  • 目次
    • セルに条件付き書式を設定する
    • セルの条件付き書式を編集する
    • セルの条件付き書式を削除する
    • セルの範囲に条件付き書式を設定する
    • 別シートのセルに条件付き書式を設定する

セルに条件付き書式を設定する

条件付き書式を設定するには「1. 条件を追加する」、「2. 条件に書式を設定する」の 2 つの手順に分けて行います。

Range の引数にセル名を指定すると、そのセルに条件付き書式を設定できます。適用先がそのセルの絶対参照になり、優先度が一番低くなります。

セル「A1」に条件付き書式を設定するには Range("A1").FormatConditions.Add を入力します。

Dim fc As FormatCondition
Set fc = Range("A1").FormatConditions.Add(xlCellValue, xlBetween, "1", "20") ' 追加して取得

Call Range("A1").FormatConditions.Add(xlCellValue, xlBetween, "1", "20")     ' 追加

Cells の引数にセルの行と列の番号を指定すると、そのセルに条件付き書式を設定できます。適用先がそのセルの絶対参照になり、優先度が一番低くなります。

行「2」、列「A」に条件付き書式を設定するには Cells(2, 1).FormatConditions.Add を入力します。

Call Cells(1, 1).FormatConditions.Add(xlCellValue, xlBetween, "1", "20") ' A1
Call Cells(2, 1).FormatConditions.Add(xlCellValue, xlBetween, "1", "20") ' A2
Call Cells(1, 2).FormatConditions.Add(xlCellValue, xlBetween, "1", "20") ' B1
Call Cells(2, 2).FormatConditions.Add(xlCellValue, xlBetween, "1", "20") ' B2
2

条件付き書式の種類に対応した Add メソッドがあります。

メソッド名 説明
Addセルの値FormatCondition
AddAboveAverage平均以上の条件AboveAverage
AddColorScaleカラー スケールColorScale
AddDatabarデータバーDatabar
AddIconSetConditionアイコン セットIconSetCondition
AddTop10上位 10 の値Top10
AddUniqueValues一意の値UniqueValues

Add メソッドは通常の条件付き書式である FormatCondition を追加します。

FormatCondition

FormatCondition を例に紹介します。

FormatConditions.Add メソッドの引数と条件は次のように対応しています。

FormatConditions.Add(Type, Operator, Formula1, Formula2)
3

Type の値に次のものを指定できます。

定数名 説明
xlCellValue1セルの値が
xlExpression2数式が
xlTextString9テキスト文字列
xlBlanksCondition10空白の条件
xlTimePeriod11期間
xlNoBlanksCondition13空白の条件なし
xlErrorsCondition16エラー条件
xlNoErrorsCondition17エラー条件なし

Operator の値は次のものがあります。

定数名 説明
xlBetween1次の値の間
xlNotBetween2次の値の間以外
xlEqual3次の値に等しい
xlNotEqual4次の値に等しくない
xlGreater5次の値より大きい
xlLess6次の値より小さい
xlGreaterEqual7次の値以上
xlLessEqual8次の値以下

次の画像のように値を一つしか入力できないときに、Formula2 を設定しようとするとエラーが発生します。Formula2 を設定できるのは Operator が xlBetween (1) と xlNotBetween (2) のときです。

4
Call Range("A1").FormatConditions.Add(xlCellValue, xlEqual, "1", "20") ' エラー

セルの条件付き書式を編集する

条件付き書式に色を付けるなど書式を編集するには FormatConditions.Add の戻り値に対して設定するか、FormatConditions から追加されている条件付き書式を取得します。

Dim fc As FormatCondition
Set fc = Range("A1").FormatConditions.Add(xlCellValue, xlBetween, "1", "20") ' 追加して取得

Set fc = Range("A1").FormatConditions(1) ' 1 つ目の条件付き書式を取得

' 条件を編集
Call fc.Modify(xlCellValue, xlEqual, "10")

' 適用先を編集
Call fc.ModifyAppliesToRange(Range("$A$1:$A$2"))

' 条件を満たす場合は停止
fc.StopIfTrue = True ' チェックする、Modify メソッドを実行すると True になる

' 書式を設定
fc.Font.Color = RGB(255, 0, 0)      ' 文字色
fc.Interior.ColorIndex = 2          ' 背景色
fc.Borders.LineStyle = xlContinuous ' 罫線を引く

色を付けるなど書式については次のページをご覧ください。

優先度を一番高くするには FormatCondition.SetFirstPriority を入力します。一番低くするには FormatCondition.SetLastPriority を入力します。

Range("A1").FormatConditions(3).SetFirstPriority ' 1 2 3 → 3 1 2
Range("A1").FormatConditions(1).SetLastPriority  ' 1 2 3 → 2 3 1

セルの条件付き書式を削除する

セルのすべての条件付き書式を削除するには FormatConditions.Delete を入力します。

指定の条件付き書式を削除するには FormatConditions(1).Delete を入力します。

Range("A1").FormatConditions.Delete ' すべての条件付き書式を削除

Range("A1").FormatConditions(2).Delete ' 2 つ目の条件付き書式を削除
Range("A1").FormatConditions(1).Delete ' 1 つ目の条件付き書式を削除

FormatConditions のインデックスは優先度の一番高いものが 1 です。

5

シート全体のセルの条件付き書式をクリアするには Cells.FormatConditions.Delete を入力します。

Cells.FormatConditions.Delete

セルの範囲に条件付き書式を設定する

セルの範囲「A1」~「B2」に同じ条件付き書式を設定するには Range("A1:B2").FormatConditions.Add または Range("A1", "B2").FormatConditions.Add を入力します。適用先がそのセルの絶対参照になります。

Call Range("A1:B2").FormatConditions.Add(xlCellValue, xlBetween, "1", "20")
Call Range("A1", "B2").FormatConditions.Add(xlCellValue, xlBetween, "1", "20")

適用先が同じ範囲のセルは 1 つの条件付き書式になります。適用先がセル「A1」~「A2」のときにセル「A1」~「A2」の FormatConditions.Count は 1 です。セル「A1」に対して編集するとセル「A2」にも反映されます。

これは Excel の操作と同じです。

適用先だけを変更するには FormatCondition.ModifyAppliesToRange を入力します。

Call Range("A1").FormatConditions(1).ModifyAppliesToRange(Range("$A$1:$A$2"))

別シートのセルに条件付き書式を設定する

シートを指定するには Worksheets の引数にシート名またはインデックスを指定します。

Sheet1 を指定するには Worksheets("Sheet1") または Worksheets(1) を入力します。

sheet1
' Sheet1 のセル「A1」に条件付き書式を設定
Call Worksheets("Sheet1").Range("A1").FormatConditions.Add(xlCellValue, xlEqual, "10")
Call Worksheets(1).Range("A1").FormatConditions.Add(xlCellValue, xlEqual, "10")

' Sheet2 のセル「A1」に条件付き書式を設定
Call Worksheets("Sheet2").Range("A1").FormatConditions.Add(xlCellValue, xlEqual, "10")
Call Worksheets(2).Range("A1").FormatConditions.Add(xlCellValue, xlEqual, "10")

Sheet1 に Range や Cells を入力すると、自身のシートのセルになります。自身を表す Me. が省略されています。

' どちらも同じコード
Range("A1").Value = "Sheet1"
Me.Range("A1").Value = "Sheet1"

標準モジュールに Range や Cells を入力すると、開いているシートのセルになります。アクティブシートを表す ActiveSheet. が省略されています。

' どちらも同じコード
Range("A1").Value = "アクティブ"
ActiveSheet.Range("A1").Value = "アクティブ"