条件付き書式をまじめに勉強してみた(2)
自動記録されたコードを編集する
前回
のつづき。
仕様
まず、自動記録されたコードは、A3セルの値しか条件判定に使うことができない、というおっそろしくしょぼいものなので、選択範囲内全てにA列のセルの値次第で書式設定をするというものに変える。仕様としては、とりあえず
- 選択された範囲について、A列の日付が土曜日か日曜日だったら、その行全てを薄いグレーで塗りつぶす。
というものにする。
元のコード
自動記録されたコードを再掲する。
リスト1
Sub Macro1() Cells.FormatConditions.Delete '……(1)' Range("A3").Select Selection.FormatConditions.Add _ Type:=xlExpression, _ Formula1:= _ "=OR(WEEKDAY($A$3)=1,WEEKDAY($A$3)=7)" '……(2)' Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority '……(3)' With Selection.FormatConditions(1).Interior '……(4)' .PatternColorIndex = xlAutomatic '……(5)' .ThemeColor = xlThemeColorDark1 '……(6)' .TintAndShade = -0.14996795556505 '……(7)' End With Selection.FormatConditions(1).StopIfTrue = False '……(8)' End Sub
コードの改良
まずは、(1)。
Cells.FormatConditions.Delete
これだと、シート上の全セルの条件を消してしまうことになる。いくらなんでもこれは乱暴なので、
Selection.FormatConditions.Delete
と、選択範囲内の条件を削除するにとどめる(でいいんですよね?)。
んで、(2)。
Selection.FormatConditions.Add _ Type:=xlExpression, _ Formula1:= _ "=OR(WEEKDAY($A$3)=1,WEEKDAY($A$3)=7)"
これだと、条件判定に使えるセルがA3セル決め打ちになってしまうので変える。
あと、選択範囲内の全てのセルに条件設定をしないといけないので、For Each ~ Nextを使う。
Dim objCell As Range For Each objCell In Selection objCell.FormatConditions.Add _ Type:=xlExpression, _ Formula1:="=OR(WEEKDAY(A" & objCell.Row & ")=1," & _ "WEEKDAY(A" & objCell.Row & ")=7)" objCell.FormatConditions(1)_ .Interior.Color = myLightGray Next
といったところか。
ちなみに、「myLightGray」ってのはユーザー定義定数ってやつで、モジュールの宣言セクションに
Const myLightGray As Long = 14277081
と記述してある。
14277081というナゾの数字は、
こうしてから、
こうやって求めた。
これで、書式設定したい行に応じて選択範囲内全てのセルに条件付き書式の設定ができる。
ところで、元のコードの(3)~(8)、すなわち、
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority '……(3)' With Selection.FormatConditions(1).Interior '……(4)' .PatternColorIndex = xlAutomatic '……(5)' .ThemeColor = xlThemeColorDark1 '……(6)' .TintAndShade = -0.14996795556505 '……(7)' End With Selection.FormatConditions(1).StopIfTrue = False '……(8)'
は、(4)を除いてばっさりポアw
だって、何の役に立ってるんだか分からないんだものw
これで、全体のコードはこうなる。
リスト2
Sub setFormatConditionTest() Selection.FormatConditions.Delete Dim objCell As Range For Each objCell In Selection objCell.FormatConditions.Add _ Type:=xlExpression, _ Formula1:="=OR(WEEKDAY(A" & objCell.Row & ")=1," & _ "WEEKDAY(A" & objCell.Row & ")=7)" objCell.FormatConditions(1)_ .Interior.Color = myLightGray '……(*)' Next End Sub
ただ、どうも(*)のところがどうも気にくわない。なんか、ブサイクなんですよ。
んで、しばらくggってみたところ、FormatConditionオブジェクトの取得については、コチラによると、
FormatConditionsコレクションオブジェクトのAddメソッドは実行されると、Addメソッドで新たに追加されたばかりのFormatConditionオブジェクトを返してきます。
ということらしいので、Addメソッドの返り値であるFormatConditionオブジェクトを即変数にセットしてしまえばよいと分かった。たとえば、
Dim tgtFormatCondition As FormatCondition Set tgtFormatCondition = _ objRange.FormatConditions.Add _ (Type:=xlExpression, _ Formula1:= _ "=OR(WEEKDAY($A$3)=1,WEEKDAY($A$3)=7)")
とすれば、A3セルに条件をセットしたのと同時にその条件を変数にセットしたことになるわけだ。
あとは、変数tgtFormatConditionを利用して条件を満たす場合の書式を書けばよい。
そうして書き換えたのがコチラ。
リスト3
Sub setFormatConditionTest() Selection.FormatConditions.Delete Dim objCell As Range Dim tgtFormatCondition As FormatCondition '……(1)' For Each objCell In Selection Set tgtFormatCondition = _ objCell.FormatConditions.Add _ (Type:=xlExpression, _ Formula1:="=OR(WEEKDAY(A" & objCell.Row & ")=1," & _ "WEEKDAY(A" & objCell.Row & ")=7)") '……(2)' tgtFormatCondition.Interior.Color = myLightGray Next End Sub
ずいぶんスッキリしたぞ。
リスト3の説明
(1)の
Dim tgtFormatCondition As FormatCondition
は、FormatCondition型の変数tgtFormatConditionの準備。こんな型があったんですねえ。
(2)の
Set tgtFormatCondition = _ objCell.FormatConditions.Add _ (Type:=xlExpression, _ Formula1:="=OR(WEEKDAY(A" & objCell.Row & ")=1," & _ "WEEKDAY(A" & objCell.Row & ")=7)")
は長いけど作りは単純。右辺のFormatConditionsコレクションのAddメソッドで変数objCellが指し示すセルに条件を設定し、ということはつまり、新たにFormatConditionオブジェクトを作り出して、変数tgtFormatConditionにセットしているだけ。
TypeとかFormula1というのはAddメソッドの引数で、それぞれ数式を条件とすること、その数式、を表している。
ちなみに、Formula1プロパティの値を
"=OR(WEEKDAY(A" & objCell.Row & ")=1," & _ "WEEKDAY(A" & objCell.Row & ")=7)")
と、「=1,」の後ろで一旦ダブルクオーテーションを閉じて行継続文字で改行しているが、これは単に可読性だけの問題。本来こんなところで改行する必要はない。
実行
範囲を選択して、マクロを実行すると、
ほれ、この通り、条件付き書式が適用されている。
おわりに
まだまだ決め打ちみたいな処理しかできないので、もっと柔軟な処理ができるようにしたいなあ。