Excel VBA 条件付書式の条件満たすセル取得

質問

Excel2010のVBAで条件付書式の条件を満たすセルの番地を取得したい

具体的には、
Excel2010のあるシートのあるセル範囲(例えばA1～XFD1048576)に
条件付き書式が付けてあって、
(例えば、数式の条件が満たされたら背景色を赤色にするなど)
この条件を満たすセルに指定した書式が付けられて表示されています。
この状態で、VBAで、この条件を満たしたセルの番地を、
順番に取得したいのですが、どのように記述すればよいでしょうか。

【追記】
数式をすべてのセルに入れて検出する方法や
セルをひとつずつ数式に当てはめてみていく方法は、
セルが膨大なため容量的・時間的にNGです。
このため、条件付き書式で回避しています。
条件付き書式の判定結果である書式(この場合でしたら背景色が赤色)
で判断する必要があります。(書式は背景色が赤色でなくてもいいです)
よく分かりませんが、検索の中の書式で指定しても、
この条件付き書式の判定結果の書式はヒットしませんでした。

回答No.3

real beatin（@realbeatin） · ベストアンサー

No.2補足欄、拝見しました。

> この目的は、元々は、2つのシートの内容が、
> 完全に一致することを確認したかったのですが、
完全に【一致】するか[y/n]
だけを調べるなら、
【相違】がひとつもないか[y/n]
を調べる方が簡単ですよね？
【相違】ではなく【一致】を採る理由が、
ご説明からは読み取れないです。
さておき、
> 条件付書式では、すぐに結果が出たので、
> この結果を利用して、一致しないセルが
> あるかどうかとその場所を取得できれば、
> 2つのシートの内容が完全に一致することを
> 確認できるのではないかと考えたわけです。
お気持ちは理解できます。
なるほど、条件付き書式であれば、
即時に、結果を表示された書式に反映させることは出来ますが、
そもそも条件付き書式は、
閲覧時の表示用、或いはプリント用に用意された修飾に特化した機能ですから、
条件に合致したセル範囲を返すような属性は
残念ながら用意されていませんので、
これを分析に用いるというのは本来の目的に適うものではありません。
No.2で紹介した、range.DisplayFormat プロパティは、
ユーザーの要望に応える形でxl2010で追加された新しい機能ですが、
条件付き書式の結果としての表示された書式［以下、"表示書式"］
を利用するということだと（方法は幾つかありますが）
例えばDisplayFormatより簡単なもの、は、ありません。
表示書式を見れば、せっかく結果が表示されているのだから、
これを使いたい、ということがお望みなのでしょうけれど、
フラグやマークアップとして使える情報は保持してませんので、
速さで言うならば、結論として、
表示書式とは無縁に、愚直にＶＢＡで値を比較した方が速いです。
手元で走り書きのマクロで試してみましたが、、
　データ（数値）：二千五百万セル、
　表示書式としての塗りつぶし：八百万セル、
　という条件で、
DisplayFormatを調べた場合は、１０分ほど（戻り値は単一セル範囲多数）
　データ（数値）：二千五百万セル、
　というシートを二つ
　一致するセル：三百万セル、
　という条件で、
二つのシートの値を比較した場合は、１分ほど（結果はセル座標多数）
の所要時間でした。
セルの数に比してこの結果を膨大な時間と呼ぶかどうかは、
主観の問題ですが、私の感覚としてはこれ以上をExcelに求めるのは酷かな、と。
> セル数が膨大
もう少し具体的であれば、その概数によって、
どんな計算方法が速いのか、というお話なら出来るとは思います。
とは言え、【一致】を調べて本当は何をしたいのか、
全体を理解出来ないと提示できる情報は少ないです。
手法としては、
想定されるセル数が比較的少ない順に（概ね数万セルから数千万セル程度のスケール）
●Evaluate メソッドを介してExcel側に計算させる
　　（使用メモリが多く喰うけど、比較的高速）（結果は座標）
●第三のシートに比較する数式を展開する
　　（一致する範囲を選択するのが目的なら、最適）
●一旦HTMLテーブルとして読み込んで、正規表現やDOMで処理する
　　（一致したセルを編集するのに向いている）（結果はセルの書換え）
●配列変数に格納した値を比較する
　　（使えるメモリは有限だけど、比較的高速）（結果は座標）
●一旦、テキストファイルに書きだして（端からテキストにする）テキストを比較する
　　（ハードディスクを使うのでメモリ消費を抑えられる）
●ADODBで読み比べる。
など方法はいくつか考えられます。
> Excel2010のあるシートのあるセル範囲(例えばA1～XFD1048576)に
百億超のセルを扱うのは、Excelには（ブックとして扱うのは）まず無理ですから、
> セル数が膨大
ある程度想定される最大セル数を決めておいて、
それに合った手法を選んでいくことになると思います。
後は全体の処理や具体的な仕様にどう合わせていくかだと思いますし、
簡単さと処理速度のコストバランスをどう考えるかでしょうか。

要約すると、
条件付き書式は表示用、プリント用。
結果をフラグとして分析に使うことは難しい。
直接、値の方を比べる事になるでしょう。

題意への理解が未だ不十分なのかも知れませんが、
この程度で御容赦ください。
それではまたいつか。

お礼

>そもそも条件付き書式は、
>閲覧時の表示用、或いはプリント用に用意された修飾に特化した機能ですから、
>条件に合致したセル範囲を返すような属性は
>残念ながら用意されていませんので、

そういうことでしたか。
特定の書式がついたセルを抽出することが
できないということが分かりましたので、
値の入っているセルに限定して、
書式ではなく値で1つずつセルの内容を
確認していく方向で検討してみることにします。

いろいろ調べていただきましてありがとうございました。

回答No.2

real beatin（@realbeatin）

こんにちは。

基本的な方法としては以下のように。

' ' ///
Sub ReW9121222a()
Dim c As Range
For Each c In ActiveSheet.UsedRange
If c.DisplayFormat.Interior.Color = vbRed Then
Debug.Print c.Address(0, 0)
End If
Next
End Sub
' ' ///

もしかして、セルの結合などしてあって、
ご自身で応用が出来ない場合は、
If c.DisplayFormat.Interior.Color = vbRed Then
If c.MergeCells Then
If c.MergeArea(1).Address = c.Address Then
Debug.? c.MergeArea.Address(0, 0)
等の例を用いて工夫してみて下さい。

range.DisplayFormat プロパティ（オブジェクト）
を使って「表示された書式」をチェックする方法以外にも、
worksheet.AutoFilter オブジェクトで
列毎に色フィルターを掛けて、表示された範囲を拾う方法
の方が（列が限定されているケースなど）効率良い場合
もあるのかも知れません。
基本的な構文はマクロの記録などでも、十分なものが得られます。

> セルが膨大なため容量的・時間的にNGです。
そう聞くと、寧ろ条件付き書式を使うことを選択肢から排除する
という人、結構いると思いますし、私も俄かに同意できない面もあります。
膨大なデータを扱うからこそ、
Excelに再計算させる機会を限りなく減らし、
それによって扱い易い（使える）ブックを維持できるようにする
ということを優先的にに考えてしまいます。
xl2010でしたら、
条件付き書式を適用した範囲に絡んで、
コピー、貼付け、切り取り、等の操作をする場合は、
[条件付き書式の結合]モードで貼り付けることを意識する等、
条件付き書式の増殖を避けるように十分に注意しておかないと、
ファイルサイズと再計算の量が膨大になってしまって、
「或る日開けなくなった」
というような相談に繋がることもありますので
留意してあげてください。

何か不足があれば、補足してください。

補足

すいません。質問の仕方がまずかったです。

For Each c In ActiveSheet.UsedRangeでは、
すべてのセルをひとつずつチェックしていく
ことになってしまうため膨大な時間がかかります。
このため、セルをひとつずつチェックしなくても
条件付書式の条件を満たすセルだけを短時間で
抽出することができないかを知りたかったのです。

例えば、
条件付書式の条件を満たすセルだけを
何らかの方法
(すべてのセルをひとつずつチェックしていくのではなく、
　あるメソッドを実行するなどの方法で一瞬または短時間)
で選択することができれば、
Dim targetCell As Range
For Each targetCell In Selection.Cells
　Debug.Print targetCell.Address
Next
などで取得することができます。

UsedRangeの範囲のセルの数は膨大ですが
条件付書式の条件を満たすセルの数はわずかなので
処理は一瞬で終わります。

この目的は、元々は、2つのシートの内容が、
完全に一致することを確認したかったのですが、
セル数が膨大なため、すべてのセルをチェック
していたのでは、時間がかかりすぎていましたが、
条件付書式では、すぐに結果が出たので、
この結果を利用して、一致しないセルが
あるかどうかとその場所を取得できれば、
2つのシートの内容が完全に一致することを
確認できるのではないかと考えたわけです。

回答No.1

ushi2015

こんにちは
条件付書式で設定した背景色はInterior.ColorIndex等では検索出来ないので
Evaluate(条件の数式)で判定していくしかないと思います。

お礼

通常の背景色と同じように
条件付書式で設定された背景色も検索ができれば
何も悩まなかったのですが・・・・
ありがとうございました。