■xl2007：ModifyAppliesToRangeメソッド

EXCELVBAであるセルに設定されている条件式書式を取得したいと - 教えて！goo

あっさりと一発で解決しましたが、実はこれ手強かったンです。

Option Explicit

Sub Macro1()
　　With Sheets.Add
　　　　'A1:C1範囲に条件1を設定する
　　　　With .Range("A1:C1").FormatConditions
　　　　　　.Delete
　　　　　　With .Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:="1")
　　　　　　　　.Interior.ColorIndex = 35
　　　　　　End With
　　　　End With
　　　　'B1:C1範囲に条件2を設定する
　　　　With .Range("B1:C1").FormatConditions
　　　　　　With .Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:="2")
　　　　　　　　.Interior.ColorIndex = 36
　　　　　　End With
　　　　End With
　　　　'C1に条件3を設定する
　　　　With .Range("C1").FormatConditions
　　　　　　With .Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:="3")
　　　　　　　　.Interior.ColorIndex = 37
　　　　　　End With
　　　　End With
　　　　.Range("A1:C1").Value = [{1,2,3}]
　　　　Call FormatConditionsTest(.Range("C1"))
　　End With
End Sub
'---------------------------------------------------------------------
Sub FormatConditionsTest(ByRef r As Range)
　　Dim n As Long
　　Dim i As Long

　　With r.FormatConditions
　　　　n = .Count
　　　　ReDim ret(1 To n) As String
　　　　For i = 1 To n
　　　　　　ret(i) = .Item(i).Formula1
　　　　Next
　　End With
　　MsgBox Join(ret, vbLf)
　　Erase ret
End Sub

Macro1 実行後。




現象としては以下。

・Excel2007において、上記コードでC1セルに[条件付き書式]を設定する。
・この時、上図のように[適用先]のセル範囲が、条件ごとに違うと、[Formula1 プロパティ]が正しく取得できない。

2003では正しく取得できます。（そもそも2003には[適用先]という概念がない）

解決策としては、単純に[適用先]を揃える事が考えられます。
2007から[ModifyAppliesToRange メソッド]という[適用先]セル範囲を設定するメソッドも追加されています。
リンクQ&Aで
>どうしても、違う範囲を設定する必要がある場合、
>作業用シートや作業用ブックにコピーして
>ModifyAppliesToRangeメソッドで範囲を揃えてあげると取れます。
などと書いてますが、ここに落とし穴がありましたorz

何も考えず

Sub test1()
　　Dim fc As FormatCondition

　　ActiveSheet.Copy
　　With ActiveSheet '作業用Sheet
　　　　With .Range("C1")
　　　　　　For Each fc In .FormatConditions
　　　　　　　　fc.ModifyAppliesToRange .Item(1)
　　　　　　Next
　　　　　　Call FormatConditionsTest(.Item(1))
　　　　End With
　　End With
End Sub

...ってやると、作業用シートには変化なく、何と元シートの条件付き書式範囲がModifyされてしまうのです。
これは

Sub test2()
　　Dim wb1 As Workbook　'元のBook
　　Dim wb2 As Workbook　'作業用Book
　　Dim ws1 As Worksheet '元のSheet
　　Dim ws2 As Worksheet '作業用Sheet
　　Dim fc　As FormatCondition

　　Set wb1 = Workbooks("test1.xlsx")
　　Set wb2 = Workbooks("test2.xlsx")
　　Set ws1 = wb1.Sheets(1)
　　ws1.Copy wb2.Sheets(1)
　　Set ws2 = wb2.Sheets(1)
　　With ws2.Range("C1")
　　　　For Each fc In .FormatConditions
　　　　　　fc.ModifyAppliesToRange .Item(1)
　　　　Next
　　　　Call FormatConditionsTest(.Item(1))
　　End With

　　Set ws1 = Nothing
　　Set ws2 = Nothing
　　Set wb1 = Nothing
　　Set wb2 = Nothing
End Sub

...って感じで、くど過ぎるくらいObject指定してもダメでした。
元Bookの"test1.xlsx"の元シートの[適用先]が変更されちゃいます。





DoEventsをLoop回してもダメだったので、リンクQ&AではOnTimeメソッドで一拍おいてます。

が、その後検証してみたら、『ActiveSheetをActivateしてあげれば良い』だけなのであった..(ぉぃ
ぃや、SheetでなくてもBookやCellでもいいんですけどね。

Sub test1改()
　　Dim fc As FormatCondition

　　ActiveSheet.Copy
　　With ActiveSheet
　　　　.Activate '■
　　　　With .Range("C1")
　　　　　　For Each fc In .FormatConditions
　　　　　　　　fc.ModifyAppliesToRange .Item(1)
　　　　　　Next
　　　　　　Call FormatConditionsTest(.Item(1))
　　　　End With
　　End With
End Sub

なんという挙動不審さ。orz