条件付き書式をまじめに勉強してみた(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というナゾの数字は、

f:id:akashi_keirin:20170401222342j:plain

こうしてから、

f:id:akashi_keirin:20170401222357j:plain

こうやって求めた。

これで、書式設定したい行に応じて選択範囲内全てのセルに条件付き書式の設定ができる。

ところで、元のコードの(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,」の後ろで一旦ダブルクオーテーションを閉じて行継続文字で改行しているが、これは単に可読性だけの問題。本来こんなところで改行する必要はない。

実行

f:id:akashi_keirin:20170401222405j:plain

範囲を選択して、マクロを実行すると、

f:id:akashi_keirin:20170401222414j:plain

ほれ、この通り、条件付き書式が適用されている。

おわりに

まだまだ決め打ちみたいな処理しかできないので、もっと柔軟な処理ができるようにしたいなあ。

@akashi_keirin on Twitter