入力規則(ValidatioList)はこちら

[Excel VBA] ValidationList 入力規則

入力規則 ValidationList の呼び出し方

Excel2013以降
データ>データツール>データの入力規則
わかりにくくなっている

条件付き書式の呼び出し方

ホーム＞スタイルに大体条件付き書式がある

今回のコード

コードの使い方

開発タブを表示させマクロのセキュリティをオフにします。
適当にExcelで新規ワークシートを作ります。
VBEエディタを開き標準モジュールを作ります。

上記の3．の手順は、ALT+F11 ALT + I ALT + M とALTキーを押したままF11 I M と押すとスムーズです。

なお、今回のコードは日本語になっていないと無理です。
If Your PC Setting not Japanese, Please change code Specified.

マクロの動作原理

Sub SettingRangeFormatCondition()をまず起動します。
A1から今月の日付を入れます。31日ない月も機械的に埋めます。その場合、翌月の日がはいります。
二行目に1行目の日付の =A1の形で持ってきて、表示形式を変えます。
AHの列に祝日祭日を定めます。今月の日付の1日、5日、10日です。SheetCellRangeという名前を設定します。もっとも、この休日は本物ではありません。シミュレーションとして設定します。実際の休日、祝日は祝日法で決まります。このため内閣府のサイトで確認してAH欄に入れます。

注意点

このコードは十分実用可能ですが、注意点があります。
振替休日は設定しないと自動的に変わりません。
また月末は31日まで日付を埋めるので、手動で調整が必要です。

このように1日は祝祭日でかつ休日ですが、振替休日を設定していないので、月曜は休みになりません。実務ではAHの列に祝祭日と振替休日、年末年始をいれていきます。

FormatConditionsの注意点

今回はセルの強調表示ルールで強調の条件が数式

今回はセルの強調表示ルールで数式を使うものです。従来のExcelからあり、日付を入れるときにとても便利な条件付き書式です。

手動で設定して読み取る

まず手動で条件付き書式を設定し、色合いなどを定め、読みだして色の値、数式をチェックします。この上で設定していく方が確実です。
条件付き書式は入力規則と同様Modifyのような変更ではなく削除してから追加をする方がよいようです。
このマクロは今設定されている大量の条件付き書式を修正に使用されることを想定しています。

条件付き書式の数式はセル範囲を表す「名前」で範囲を指定できない

例外的に、同じワークシート内に設定されたセル範囲につけられた「名前」は使えます。

VBAとは離れるが、IFを入れ子にして、複数の条件を一つの条件式で表すことができる。

今回土曜日(7)と日曜日(1)を別個の条件式書式として定めましたが、これは入れ子でもできます。

この数式を以下のように変えます
=IF(WeekDay(A1)=1,True,IF(Weekday(A1)=7,True,False))=True

ScreenshotはFalse)= Trueになっていますが誤りです。
このように変えると、あとの条件付き書式は削除していいことになります。

必ず優先順位1から設定する。

優先順位は必ず定めます。変更はあとからできるのですが、優先順位1番目の条件付き書式から記述して行く方が確実です。

必ず定める優先順位と.StopIfTrue = False

優先順位はなぜか31から始まります。
手動で入力したときの標準はFalseですが、VBAの標準はTrueです。このため必ずFalseにします。Trueにすると、その条件に合致しても次の条件に流れていきます。例えば今月の12日が祝日で祝日を赤、偶数日を青という条件を設定し、優先順位を前者が1位で、後者が2位とします。この場合、1位にStopIfTrue = Falseを設定しないと、12日は青になります。

妙なところでおりこうさんなExcel

条件付き書式は範囲に1度に設定できますが、参照が自動的に変わります。VBAでは範囲の一番左か一番上でどのような数式になっているを設定すると、自動的に参照する場所が変わります。

Addでセットする流れ

設定するRangeを定める。例 A1:C1など。複数のセルを指定するのはコレクションになります。
コレクション.Add範囲、式

set 変数 = コレクション(１から始まるインデックス）


With 変数
この間に優先順位
.StopIfTrue = False
さらにWith interior
      .color 色の値
     End with
End With

コレクション.Add範囲、式
set 変数 = コレクション(2）
これを繰り返す。

## コード

Sub SettingRangeFormatCondition()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = ActiveSheet
Dim fcs As FormatCondition, fcss As FormatConditions
Dim R As Range, i As Long
Dim fcc
Dim Ar, ia As Long, adr As String, adChr As String, arr As Variant
Dim nns As Excel.Names, nn As Name
ws.Range("A1:A2").Clear
ws.Cells(1, 1) = DateSerial(Year(Date), Month(Date), 1)
ws.Cells(1, 1).NumberFormatLocal = "d"
ws.Cells(2, 1).Formula = "= A1"
ws.Cells(2, 1).NumberFormatLocal = "aaa" 'Japanese
'ws.Cells(2, 1).NumberFormatLocal = "ddd" 'InterNational
For i = 2 To 31
ws.Cells(1, i).Select
With Selection
.Value = DateAdd("d", Cells(1, i - 1).Value, 1)
.NumberFormatLocal = "d"
adr = ActiveCell.Address
arr = Split(adr, "$")
adChr = arr(1)
ws.Cells(2, i).Formula = "=" & adChr & 1
ws.Cells(2, i).NumberFormatLocal = "aaa" 'Japanese
'ws.Cells(2, i).NumberFormatLocal = "ddd" 'InterNational
End With
Next i
ws.Range("AH1:AH3").Clear
For Each nn In ws.Names
If nn.Name = "SheetCellRange" Then ws.Range("SheetCellRange").Delete
Next
With ws
.Range("AH1") = .Range("A1").Value
.Range("AH2") = DateAdd("d", .Range("A1").Value, 4)
.Range("AH3") = DateAdd("d", .Range("AH2").Value, 5)
.Columns("AH:AH").EntireColumn.AutoFit
End With
With ws.Range("AH1:AH3").CurrentRegion
.Name = .Worksheet.Name & "!SheetCellRange"
End With
Ar = Split("2", ",") '曜日が入っている最初のセルの行番号 12か月分を想定し、配列にしている
For ia = LBound(Ar) To UBound(Ar) ' 配列の最初から最後まで回す
Set R = ws.Range("A" & Ar(ia) & ":AE" & Ar(ia))
Set fcss = R.FormatConditions '範囲内のセルのすべての条件式をfcssに代入
fcss.Delete 'いったん消去する

'//// 祝日の条件付き書式 ///
'条件付き書式は 最初に式を追加し、詳細な設定をそのあと加える
fcss.Add Type:=2, Formula1:="=COUNTIF($AH$1:$AH$3,A" & Ar(ia) & ")>=1" ' Japanese 祝祭日の条件付き書式の式を追加する
'fcss.Add Type:=2, Formula1:="=COUNTIF(SheetCellRange,A" & Ar(ia) & ")>=1" 'International
Set fcs = fcss.Item(1) '最初に追加したものは1からカウントする
With fcs 'Withでfcsを省略
.Priority = 30 + fcss.Count '優先順位。1つ追加しているので、31になる 順番としては１位
.StopIfTrue = False
With .Interior
.Color = 10066431 '色の値
End With
End With
'土曜日、日曜日を追加上と同じ要領
'//// 土曜日の条件付き書式 ///
fcss.Add Type:=2, Formula1:="=TEXT(A" & Ar(ia) - 1 & ",""aaaa"")=""日曜日""" 'Japanese
'fcss.Add Type:=2, Formula1:="=Weekday(A" & Ar(ia) - 1 & ")=1" 'InterNational
'C15ならC14の日付を表示形式で○曜日に変えてその値で色を変える。aaaaは表示形式の指定方法のひとつで、日付を○曜日に変える。
Set fcs = fcss.Item(2)
With fcs
.Priority = 30 + fcss.Count: .StopIfTrue = False 'VBAは手動とは違いTrueが標準のため、自信があるならFalseにする
With .Interior
.Color = 10066431
End With
End With
fcss.Add Type:=2, Formula1:="=TEXT(A" & Ar(ia) & ",""aaaa"")=""土曜日""" 'Japnese
'fcss.Add Type:=2, Formula1:="=Weekday(A" & Ar(ia) - 1 & ")=7" 'InterNational
Set fcs = fcss.Item(3)
With fcs
.Priority = 30 + fcss.Count: .StopIfTrue = False
With .Interior
.Color = 10066431
End With
End With
Next ia
End Sub


Sub ReadRangeFormatCondition() 
’Show On Immediate Window If activecell has Formatcondition
'Activecell に条件付き書式があれば、イミディエイトウィンドウに表示します。
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = ActiveSheet
Dim fcs As FormatCondition, fcss As FormatConditions
Dim R As Range, i As Long
Set R = Range(ActiveCell.Address)
Set fcss = R.FormatConditions
If fcss.Count > 0 Then

For i = 1 To fcss.Count
Set fcs = fcss.Item(i)
On Error Resume Next
Debug.Print fcs.AppliesTo.Address
Debug.Print fcs.Borders.Count
Debug.Print fcs.DateOperator
Debug.Print "Formula1", fcs.Formula1
Debug.Print "Formula2", fcs.Formula2
Debug.Print "fcs.Interior", fcs.Interior.ColorIndex, fcs.Interior.ThemeColor
Debug.Print fcs.NumberFormat
Debug.Print fcs.Priority
Debug.Print fcs.PTCondition
Debug.Print fcs.ScopeType
Debug.Print fcs.StopIfTrue
Debug.Print fcs.Text
Debug.Print "TextOperator", fcs.TextOperator
Debug.Print "Type: ", fcs.Type
If Err.Number <> 0 Then Debug.Print "Err.Number : ",Err.Number,Err.Description:Err.Clear:Else:Debug.Print "---- NO Error ------"
Debug.Print "--------------"
Next
End If
End Sub

FormatConditions

FormatConditions.Add メソッド (Excel)

Add(Type,Operator , Formula1 , Formula2 )
カラースケールなどは違うメソッドで追加される。

名前	値/必須省略	説明
Type	必須	XlFormatConditionType セル値またはオブジェクト式のどちらを基に条件付き書式を設定するかを指定します。
Operator	省略可能	バリアント型条件付き書式の演算子です。使用できる定数は XlFormatConditionOperator クラスの xlBetween、xlEqual、xlGreater、xlGreaterEqual、xlLess、xlLessEqual、xlNotBetween、xlNotEqual のいずれかです。Type に xlExpression を指定した場合、Operator 引数は無視されます。
Formula1	省略可能	バリアント型条件付き書式に関連させる値またはオブジェクト式を指定します。定数値、文字列値、セル参照、または数式を指定できます。
Formula2	省略可能	バリアント型引数 Operator に xlBetween または xlNotBetween を指定した場合、条件付き書式の 2 番目の部分に関連させる値またはオブジェクト式を指定します。それ以外を指定した場合、この引数は無視されます。定数値、文字列値、セル参照、または数式を指定できます。

XlFormatConditionType 列挙 (Excel)
セル値または演算式のどちらを基に条件付き書式を設定するかを指定します。

名前	値	説明
xlAboveAverageCondition	12	ある一定の値を超えると発動する
xlBlanksCondition	10	空白だと発動する
xlCellValue	1	セルの値
xlColorScale	3	カラースケール最小最大中間、色で決める。
xlDatabar	4	データバー
xlErrorsCondition	16	エラー条件
xlExpression	2	数式で演算しその値を条件に発動する。今回のメイン
xlIconSet	6	アイコンセット。閾値により３~5のグループに分類する。
xlNoBlanksCondition	13	空白の条件なし
xlNoErrorsCondition	17	エラー条件なし
xlTextString	9	テキスト文字列
xlTimePeriod	11	期間
xlTop10	5	上から 10 個の値のみに発動する
xlUniqueValues	8	指定した範囲で一意の値なら発動する

FormatConditions.AddIconSetCondition メソッド

指定された範囲の IconSetConditionオブジェクトを追加します。
アイコンセットを使用すると、しきい値によりデータを 3 ～ 5 つのカテゴリに分類して各カテゴリの内容を示唆できるようできます。各アイコンは、ある範囲の値を表します。たとえば、3 つの矢印によるアイコンセットを使用する場合、赤の上向き矢印は大きい値を、黄色の横向き矢印は中程度の値を、緑の下向き矢印は小さい値を表します。

FormatConditions.AddDatabar メソッド (Excel)

指定された最大最小の中のどの範囲に位置するかを示す棒グラフを表示させる。

FormatConditions.AddUniqueValues メソッド (Excel)

UniqueValues オブジェクトを使用して、一意または重複した値を含むセルを素早く表示できます。
FormatConditions.AddTop10 メソッド (Excel)
Top10オブジェクトという頭の悪い名前の条件付き書式を追加する。
Top10 と言いながら下位10位とかTop25位とか設定できる。ランクをパーセントの値で決定することもできる。またランクを計算するときに列なのか行なのかすべてなのかを設定するTop10.CalcFor プロパティと定数を設定するときがある。

FormatConditon ProPerties

FormatCondition.AppliesTo プロパティ (Excel)
書式ルールが適用されているセル範囲を表す Range オブジェクトを返します。($A1:$F1のような絶対番地)
FormatCondition.DateOperator プロパティ (Excel)
書式条件で使用する日付演算子を指定します。値の取得と設定が可能です。

FormatCondition.PTCondition プロパティ (Excel)
条件付き書式がピボットテーブルに適用されるかどうかを表すブール型 ( Boolean ) の値を返します。値の取得のみ可能です。
※Addの時は設定しなくてよい

FormatCondition.Operator プロパティ (Excel)
条件付き書式の演算子を表す長整数型 ( Long ) の値を返します。
次の使用例は、セル範囲 E1:E10 の条件付き書式 1 が "5 未満の値" に指定されている場合、数式を変更します。X以上ｙ未満のように以上、未満が複数ある場合に使用する

XlFormatConditionOperator 列挙 (Excel)

メンバ名	説明	値
xlBetween	BETWEEN (2 つの数式が指定されている場合のみ使用可)	1
xlEqual	等しい	3
xlGreater	超える	5
xlGreaterEqual	以上	7
xlLess	未満	6
xlLessEqual	以下	8
xlNotBetween	NOT BETWEEN (2 つの数式が指定されている場合のみ使用可)	2
xlNotEqual	等しくない	4

With Worksheets(1).Range("e1:e10").FormatConditions(1)
If .Operator = xlLess And .Formula1 = "5" Then
.Modify xlCellValue, xlBetween, "5", "15"
End If
End With

FormatCondition.ScopeType プロパティ (Excel)
条件付き書式がピボットテーブルに適用される場合、その適用範囲を表す XlPivotConditionScope クラスの定数を取得、または設定します。

FormatCondition.StopIfTrue プロパティ (Excel)
現在のルールが True に評価された場合、そのセルの書式ルールをさらに評価するかどうかを表すブール型 ( Boolean ) の値を取得、または設定します。

構文

Expression.StopIfTrue

Expresson FormatCondition オブジェクトを表す変数。

注釈

以前のバージョンとの互換性を保つために、このプロパティの既定値は、 True に設定されています。これは、ユーザーインターフェイスで書式ルールを作成した場合と逆の状態となります。

FormatCondition.TextOperator プロパティ (Excel)
XlContainsOperator 列挙の定数の 1 つを取得または設定し、条件付き書式ルールで実行されるテキスト検索を指定します。

名前	値	説明
xlBeginsWith	2	指定した値から開始します。
xlContains	0	指定した値を含みます。
xlDoesNotContain	1	指定した値を含みません。
xlEndsWith	3	指定した値で終了します。

FormatCondition.Text プロパティ (Excel)
条件付き書式ルールで使用されるテキスト文字列を指定する文字列型 ( String ) の値を設定します。
Type プロパティが xlTextString に設定されていない場合、このプロパティは無視されます。

そのほかの主な条件付き書式

FormatConditions.AddAboveAverage

FormatConditions.AddAboveAverage メソッド (Excel)
指定された範囲の条件付き書式ルールを示す新しい AboveAverage オブジェクトを返します。
AboveAverage オブジェクトは、セル範囲内で、平均または標準偏差より上または下の値を見つけるために使用されます。たとえば、年次の業績評価から成績が平均より上の社員を特定したり、品質評価で 2 つの標準偏差よりも下に入る二次材料を特定したりできます。
AboveAverage.AboveBelow プロパティ (Excel)
条件付き書式ルールが範囲の平均または標準偏差を上回るまたは下回るセル値を探すかどうか指定して、 XlAboveBelow 列挙の定数の 1 つを取得または設定します。
AboveAverage.NumStdDev プロパティ (Excel)
AboveAverage オブジェクトの標準偏差数値を設定します。値の取得および設定が可能です。長整数型 ( Long ) の値を使用します。

FormatConditions.AddColorScale メソッド (Excel)
セルの色でグラデーションを使用して、選択した範囲内に含まれるセルの値の相対的な違いを示す条件付き書式ルールを表す新しい ColorScale オブジェクトを返します。

FormatCondition.ModifyAppliesToRange メソッド (Excel)
条件付き書式のルールが適用されるセル範囲を設定します。

注釈

セル範囲は、A1 参照形式で、 FormatConditions コレクションの親となるシート内の範囲を指定する必要があります。セル範囲には、範囲を表す演算子 (:)、共通部分を表す演算子 (スペース)、または複数の範囲を表す演算子 (,) を含めることができます。ドル記号 ($) は含めることはできますが、無視されます。

セル範囲の一部にローカルに定義した名前を使用できますが、その名前はコード記述時の言語で定義された名前である必要があります。

ColorScale オブジェクト

ColorScale
+-ColorScaleCriteria
+- ColorScaleCriterion
AddColorScaleで追加する
カラースケールの最小、中間、最高しきい値を指定

参考

Dateadd関数 relief
セルに名前を定義する(Nameプロパティ) Moug
VBA セルの条件付き書式を取得する TipsFound
条件付書式その３
 VBScriptでExcelに条件付き書式を設定する