ExcelVBA異なる適用先範囲の条件付き書式取得

質問

お世話になります。ExcelVBA（Excel2007）にて質問となります。

A1:E1セルにに「1」、「2」、「-」、「4」、「5」と入っており、
条件付き書式で以下の条件が登録されている場合、
FormatConditionsコレクションのFormula1で目的の数式を取得出来ません。
条件１：数式「=A1=1」の適用先「=$A$1」
条件２：数式「=A1="-"」の適用先「=$A$1:$E$1」

イミディウィンドウで実行した結果、次のようになります。
～～～～～～～～～～～～～～～～～～～～～～～～
debug.print range("A1").FormatConditions.Count
2
debug.print range("A1").FormatConditions(1).Formula1
=A1="-"
debug.print range("A1").FormatConditions(2).Formula1
=A1="-"
～～～～～～～～～～～～～～～～～～～～～～～～

セルA1を照査対象とした場合、count数は２と得られるのですが、
FormatConditions(1).Formula1で「=A1="-"」が取得されます。
「=A1=1」を取得する方法はどうすればいいでしょうか？

条件２の適用先を「=$A$1」とした場合は意図した値を取得できました。
～～～～～～～～～～～～～～～～～～～～～～～～
debug.print range("A1").FormatConditions.Count
2
debug.print range("A1").FormatConditions(1).Formula1
=A1=1
debug.print range("A1").FormatConditions(2).Formula1
=A1="-"
～～～～～～～～～～～～～～～～～～～～～～～～

回答No.1

imogasi

以下は回答ではなくてすみません。
＝＝＝
日時も経過し、回答の出現は望みにくいと思われ、質問文を再整理してわかりやすく表現して、目的なども入れて、再質問をしてはどうでしょう。
こちらは締め切ること。
目的とは、なぜ条件付き書式の設定況を調べようということになったのですか。多分書式設定がうまく行かなかったからですか。知識欲からですか。
これを書いておくと、回答者は理解しやすいと思う。
ーー
質問をみて、回答がつけば、小生も勉強しようと思っていましたが、しかしまだ回答がないようですね。めづらしいケースですね。
　私がよくわからなかった点は、ＶＢＡの条件付き書式の「設定」部分のＶＢＡコードは、どう書いたのか、省かれていてよくわからないことです。
普通は質問に書くでしょう。
range("A1").FormatConditionsAdd　以下略
range("A1:E1").FormatConditionsAdd
2つなのかな。
また「セルの値で」（xlCellValue)で済むと思うものを、「数式が」(xlExpression｠を使ったのですか。
ーー
　私は質問の回答候補者（読者）側で再現性（再現できたのか）に疑問を持ちました。
設定時のアクチブセルはどうなっていましたか（今となってはわからないかもしれないが）。普通は指定セル範囲の左上隅アクチブにして式を設定（操作）するが、これが何かの拍子にづれていると思わぬ設定になってしまう、という、シートへ向かって操作で条件付き書式を設定するときの経験（エクセル質問回答時に得た経験があります。
範囲指定したセル範囲の、それ（アクチブセル）以外のセルでは、「式を他セルに複写するときと同じ仕組み」で、セル指定が（番地に＄なしの場合のことですが）変化するのはご存知ですか。意識的にＡ１セルをアクチブにしてＶＢＡコードを書くと当でしょうか。
ーー
条件付き書式は、実用的にはシートに向かっての手操作をした方が早い。マクロの記録を取ってコードを勉強する方法もおすすめ。
ーー
私が本質問に誘発されてやってみたコードを上げます。結局質問で言っていることはわからなかったが、何かお役に立てば。
Sub test01()
Range("A1").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="1"
Range("A1").FormatConditions(1).Interior.ColorIndex = 3 ' 文字を赤色
Range("C1").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="-"
Range("C1").FormatConditions(1).Interior.ColorIndex = 6 ' 文字を黄色
End Sub
Sub test02()
Range("A1:E1").FormatConditions.Delete
Range("A1:E1").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="-"
Range("A1:E1").FormatConditions(1).Interior.ColorIndex = 3 ' 文字を赤色
End Sub
Sub test03()
Range("A1:E1").FormatConditions.Delete
Range("A1:E1").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="4"
Range("A1:E1").FormatConditions(1).Interior.ColorIndex = 3 ' 文字を赤色
End Sub
Sub test04()
Range("A1:E1").FormatConditions.Delete
End Sub
Sub test05()
MsgBox Range("a1:E1").FormatConditions.Count
MsgBox xlCellValue
MsgBox xlEqual
For Each cl In Range("a1:E1")
'cl.FormatConditions.Count
MsgBox cl.FormatConditions(1).Type & " " & cl.FormatConditions(1).Operator & " " & cl.FormatConditions(1).Formula1
'MsgBox cl.FormatConditions(2).Type & " " & cl.FormatConditions(2).Operator & " " & cl.FormatConditions(2).Formula1
Next
End Sub

Sub test06()
Range("A1:E1").FormatConditions.Delete
Range("A1:E1").FormatConditions.Add Type:=xlExpression, Formula1:="=R[0]C[0]=4"
Range("A1:E1").FormatConditions(1).Interior.ColorIndex = 3 ' 文字を赤色
End Sub
Sub test09()
'MsgBox Range("a1:E1").FormatConditions.Count
'MsgBox xlCellValue
'MsgBox xlEqual
For Each cl In Range("a1:E1")
'cl.FormatConditions.Count
MsgBox cl.FormatConditions(1).Type
MsgBox cl.FormatConditions(1).Formula1
MsgBox cl.FormatConditions(2).Type
MsgBox cl.FormatConditions(2).Formula1
'MsgBox cl.FormatConditions(2).Type & " " & cl.FormatConditions(2).Operator & " " & cl.FormatConditions(2).Formula1
Next
End Sub
Sub test10()
Range("A1:E1").FormatConditions.Delete
Range("A1:E1").FormatConditions.Add Type:=xlExpression, Formula1:="=A1=4"
Range("A1:E1").FormatConditions(1).Interior.ColorIndex = 3 ' 文字を赤色
End Sub
Sub test11()
Range("A1:E1").FormatConditions.Delete
Range("A1:E1").FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="-"
Range("A1:E1").FormatConditions(1).Interior.ColorIndex = 5 ' 文字を赤色
Range("A1:E1").FormatConditions.Add Type:=xlExpression, Formula1:="=A1=4"
Range("A1:E1").FormatConditions(2).Interior.ColorIndex = 6 ' 文字を黄色
'---
Dim fcs As FormatConditions
Set fcs = Range("A1:E1").FormatConditions
Set fc1 = fcs(1) ' 1 つ目の条件付き書式を取得
Set fc2 = fcs(2) '

ty = fc1.Type ' 条件の種類
MsgBox ty
op = fc1.Operator ' 条件
MsgBox op
s1 = fc1.Formula1 ' 1 つ目の値
MsgBox s1
's2 = fc.Formula2 ' 2 つ目の値
'MsgBox s2
'----
ty = fc2.Type ' 条件の種類
MsgBox ty
op = fc2.Operator ' 条件
MsgBox op
s1 = fc2.Formula1 ' 1 つ目の値
MsgBox s1
End Sub
Sub test12()
Range("A1:E1").FormatConditions.Delete
'Range("A1:E1").FormatConditions.Add Type:=xlExpression, Formula1:="=A1=" & "-"
Range("A1:E1").FormatConditions.Add Type:=xlValue, Formula1:="-"
Range("A1:E1").FormatConditions(1).Interior.ColorIndex = 3 ' 文字を黄色
End Sub
ーー
test10()あたりが参考になればよいですが。

お礼

回答ありがとうございます。
質問の提示方についてのアドバイス、参考のコード感謝いたします。

>目的なども入れて、再質問をしてはどうでしょう。
>こちらは締め切ること。

ご回答いただいたコードを参照・検証させて頂いたうえ、
詳細は改めて別の質問で上げさせて頂くか今回の質問の補足にてご報告させて頂きます。
（ご回答頂いたコードを検証するお時間を頂いた後に、今回の質問を締め切らせて頂きます）

>目的とは、なぜ条件付き書式の設定況を調べようということになったのですか。

目的についてですが、
最終の様式で使用する目的シートへ作成されているデータが、セルの移動により条件付き書式を設定していてもコピーや行列挿入等で崩れてしまうため、条件付き書式をVBAで再設定する事が目的となります。

ただし、以下の現状により制約があります。
(1)条件付き書式で適応しているのはセルの背景色のみであるため、文字色、表示形式、罫線等を適応済みの目的シートから条件付き書式へのシートへの値コピーでは対応できない。
(2)条件付き書式の条件式が整理の都合により修正・変更されるため、VBAによる条件付き書式の作成が出来ない。

よって、実際の様式シートから条件付き書式の設定を取得した設定シートを作成し、別途条件付き書式の設定プロシージャを用いて目的シートへ条件付き書式を再適用する方法を検討しております。

>多分書式設定がうまく行かなかったからですか。知識欲からですか。

上記目的により先ずは設定ではなく設定値の取得を目的としていたため、「取得」部分に概念をおいて検証しておりました。
今回の質問では、1つのセルに異なる適用先の条件付き書式が設定されている場合に
各条件の適用先範囲と条件式を取得することが出来るようになるための「知識欲」も兼ねております。

>私がよくわからなかった点は、ＶＢＡの条件付き書式の「設定」部分のＶＢＡコードは、
>どう書いたのか、省かれていてよくわからないことです。
>普通は質問に書くでしょう。
>range("A1").FormatConditionsAdd　以下略
>range("A1:E1").FormatConditionsAdd
>2つなのかな。

取得の際に使用しているコードは次のようにしています。
i = 0
For Each myRng In .Range("A1:E1")
　　i = i + 1
　　Set fcs = myRng.FormatConditions
　　For j = 0 To fcs.Count - 1
　　　　Cells(i, j + 5) = fcs(j + 1).Formula1 '出力先のセルへ条件式の書き出し
　　Next j
Next myRng

条件付き書式の「適用先」範囲が不定なため、指定範囲の各セルを調べて条件式を書出す方法でしておりますが、その際にA1セルを調べた際、今回の質問にあるように「A1=1」の場合を取得出来ず、FormatConditions(1)、(2)共に「A1="-"」が取得されてしまいます。

様式シートに対する実際の設定はVBAでは行っておりませんが、取得した内容を基に再設定する場合のコードは以下を想定しています。

　　Range("A1:E1").Select
　　With Selection
　　　　.FormatConditions.Add Type:=xlExpression, Formula1:="=A1=""-"""
　　　　.FormatConditions(.FormatConditions.Count).SetFirstPriority
　　　　.FormatConditions(1).Interior.Color = RGB(255, 255, 0)
　　　　.FormatConditions(1).StopIfTrue = True
　　End With
　　Range("A1").Select
　　With Selection
　　　　.FormatConditions.Add Type:=xlExpression, Formula1:="=A1=1"
　　　　.FormatConditions(.FormatConditions.Count).SetFirstPriority
　　　　.FormatConditions(1).Interior.Color = RGB(0, 255, 0)
　　　　.FormatConditions(1).StopIfTrue = True
　　End With

この方法によって取得した物を再設定する際に、各セルアドレスと、そのセルに設定されている条件式内のセルアドレスに相対的なズレが生じてしまうため、うまく再設定出来るかどうかという点のほうが問題になりそうですが。

>また「セルの値で」（xlCellValue)で済むと思うものを、
>「数式が」(xlExpression｠を使ったのですか。

質問では簡易的にA1:E1で取得時に目的の条件式を取得出来なかったことを再現するため簡略化したデータを使用しておりますが、
実データでは
「=OR(AND(LEN(E3)>0,LEN(V3&AI3)=0),AND(LEN(V3)>0,LEN(AI3)>0,JIS(E3)<>JIS(V3),JIS(E3)<>JIS(AI3)))」
のような条件式が設定されている条件付き書式になるため、セルの値比較ではなく、
条件式の指定である「数式が」(xlExpression｠で設定しています。

以下の妥協案も検討しております。
・セルに適応されている条件付き書式の適用先範囲を統一させる。
　（同じ「適用先」が設定された複数条件の場合、書き出し、再設定はうまく機能しています）
・様式シートをベースにして、値、表示形式、罫線をVBAで転記する方法にする。