Excel2010 数式判定の条件付き書式をExcelVBAで取得する方法を教えてください。

Question

Excel2003にて作成された数式判定の条件付き書式を含むブックを読み込んで

背景色が変更されているか否かを順に判定するExcelVBAを作成していますが，

Excel2010環境で実行すると正しい結果となりません。

Excel2003環境ではFormatConditionオブジェクトのFormula1プロパティにて数式を判定し，

Evaluateメソッドにて数式を評価することが出来るのですが，

Excel2010環境ではFormatConditionオブジェクトのFormula1プロパティにて取得できる数式

がExcel2003環境と異なり「条件付き書式ルール」の数式が返却されてしまい，正しく評価する

事が出来ません。

読み込むブックに設定されている条件付き書式の数式は相対参照の数式となっており，

Excel2010では「条件付き書式ルール」にて纏められています。

「条件付き書式ルール」にて纏められていることにより，ルール適用範囲内ではどこで数式を

取得しようとしても同じ可変前の数式が返却されることにより正しく評価できないことが分かりました。

試しに，数式を取得する直前にModifyAppliesToRangeメソッドを使用し，ルール適用範囲を

ターゲットとなるセルのみにしてFormula1プロパティより数式を取得すると，そのセル用に可変した

数式が取得できたのですが，条件付き書式ルールが複数設定されているセルの場合，FormatConditionsコレクション

の中身が入れ替わるまたは書き換わるといった事象が発生しました。

読み込むブックは外部より都度送付されるブックなので条件付き書式を絶対参照に変更する等の手作業は

避けたいです。

また，検証用にExcel2003環境を使用しましたが，実行環境はExcel2010環境のみとなります。

長文となり申し訳ありません。

正しく処理出来る様にしたいのですが，条件付き書式のVBAでの扱いに明るい方，

ご教示下さいます様よろしくお願い致します。

Answer 1

再現しました。

連続エリアにコピーするとそのエリアの先頭のセルを前提とした式になるようですね。

そう言われて見れば、2007以降は条件付き書式をコピー先のセルで確認するとオリジナルの式が表示され、機能的にはそのセルに読み替えられて機能しているのを思い出しました。

これは一種のサービスだと思うのですが、それを実現するために、Formulaも共通にされちゃったようですね。

この手の話はMSに言っても仕様といわれるだけでしょうし、そもそもVBA自体サービス機能みたいなものですからバージョンによる違いとしか言いようがありませんからそう割り切って対応するしかないと思います。

残念ながら、私はこれについての解決策は持ち合わせていませんし、存在しないと思います。

Applytoなどは2003には存在しませんし、実際の実行時の環境を判別してコンパイル定数にするわけにもいきませんから、2003用と2007以降用のブックを用意し、使い分けるしかないのではないかと思います。

Answer 2

返信ありがとうございます。

書式には「=WEEKDAY(Z$9,2)>5」といった式を複数設定していますが，

試していただいた条件付き書式でも貼り付け方を変えるだけで発生しました。

貼り付け方を以下のようにして試して頂けませんか？

　　A1をコピーして数カ所に貼り付け

　　　　　　　　　　↓

　　A1をコピーしてA1～C5を選択して貼り付け

A1の値を1とし，C5の値を2としてC5のセルで色を取得しようとすると，

赤と帰ってくるのをご確認いただけると思います。

よろしくお願いいたします。

Answer 3

まだ分かってないみたいです。

次の様なテストをしてみました。

A1に次の条件付き書式を設定

１．＝A1=1　パターン赤

２．＝A1=2　パターン青

A1をコピーして数カ所に貼り付け

テストコードは

Sub aaa()

Dim i As Long

  Dim myColor2 As Variant   

  mycolor = myColor2

  With ActiveCell

    If .Count > 1 Then Exit Sub

    If .FormatConditions.Count = 0 Then Exit Sub

    Dim formula As Variant

      For i = 1 To .FormatConditions.Count

        formula = .FormatConditions(i).Formula1

        MsgBox formula

        MsgBox Evaluate(formula)

        If Evaluate(formula) Then

          mycolor = .FormatConditions(i).Interior.Color

          MsgBox mycolor

          Exit For

        End If

      Next

  End With

End Sub

上記はのコードで2003でも2010でも選択されているセルの色をちゃんと取得できました。

書式にはどういう式を設定されておられるのでしょうか？

Answer 4

y sakudaさん

返信ありがとうございます。

コードを投稿いたします。

Targetに判定したいセルを設定して以下のサブを呼び出し，

myColorにて設定された背景色を取得しようとしています。

よろしくお願いいたします。

'*---------------------------------------------------------------*

'* 処理名：getConditionalFormatColor

'* 概　要：条件付書式にて変更されるセル背景色の取得処理

'*

'* 引数１：対象セル(IN)

'* 引数２：カラー　(OUT)

'*---------------------------------------------------------------*

Sub getConditionalFormatColor(ByVal Target As Range, myColor As Variant)

  Dim i As Long

  Dim myColor2 As Variant

  myColor = myColor2

  With Target

    .Activate

    If .Count > 1 Then Exit Sub

    If .FormatConditions.Count = 0 Then Exit Sub

    Dim formula As Variant

    If Application.Version <= 11 Then

      ' Excel2003以前

      For i = 1 To .FormatConditions.Count

        formula = .FormatConditions(i).Formula1

        If Evaluate(formula) Then

          myColor = .FormatConditions(i).Interior.Color

          Exit For

        End If

      Next

    Else

      ' 2007以降では条件付き書式の数式を取得するFormula1では条件付き書式ルールの

      ' 数式を取得してしまい、実際に実行されている数式を取得することができない。

      ' 条件付き書式ルールの適用範囲を強制的に変更することにより対応。

      For i = 1 To .FormatConditions.Count

        Dim sRange1 As Range

        Dim sRange2 As Range

        Set sRange1 = .FormatConditions(i).AppliesTo

        Set sRange2 = Target

        .FormatConditions(i).ModifyAppliesToRange sRange2

        formula = .FormatConditions(i).Formula1

        .FormatConditions(i).ModifyAppliesToRange sRange1

        If Evaluate(formula) Then

          myColor = .FormatConditions(i).Interior.Color

          Exit For

        End If

      Next

    End If

    If myColor = 16711935 Then

      MsgBox "条件付き書式でピンクです。"

    ElseIf myColor = 16777215 Then

      MsgBox "条件付き書式で白です。"

    ElseIf Not IsEmpty(myColor) Then

      MsgBox "条件付書式でピンク・白以外の色です。"

    Else

      MsgBox "自動=条件付き書式で色が変わっていません。"

    End If

  End With

End Sub

Answer 5

良く分かりません。

2003の方のでよろしいですから、コードとどういう状況でどういう結果を求められているのか具体的に投稿願います。