条件付き書式をまじめに勉強してみた(2)
自動記録されたコードを編集する
前回
のつづき。
仕様
まず、自動記録されたコードは、Ａ３セルの値しか条件判定に使うことができない、というおっそろしくしょぼいものなので、選択範囲内全てにＡ列のセルの値次第で書式設定をするというものに変える。仕様としては、とりあえず
- 選択された範囲について、Ａ列の日付が土曜日か日曜日だったら、その行全てを薄いグレーで塗りつぶす。
というものにする。
元のコード
自動記録されたコードを再掲する。
リスト１
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)"
これだと、条件判定に使えるセルがＡ３セル決め打ちになってしまうので変える。
あと、選択範囲内の全てのセルに条件設定をしないといけないので、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)を除いてばっさりポアｗ
だって、何の役に立ってるんだか分からないんだものｗ
これで、全体のコードはこうなる。
リスト２
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)")
とすれば、Ａ３セルに条件をセットしたのと同時にその条件を変数にセットしたことになるわけだ。
あとは、変数tgtFormatConditionを利用して条件を満たす場合の書式を書けばよい。
そうして書き換えたのがコチラ。
リスト３
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
ずいぶんスッキリしたぞ。
リスト３の説明
(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,」の後ろで一旦ダブルクオーテーションを閉じて行継続文字で改行しているが、これは単に可読性だけの問題。本来こんなところで改行する必要はない。
実行
範囲を選択して、マクロを実行すると、
ほれ、この通り、条件付き書式が適用されている。
おわりに
まだまだ決め打ちみたいな処理しかできないので、もっと柔軟な処理ができるようにしたいなあ。
