こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

Excelの条件付き書式設定とワークシート関数

Excelのシート上で条件付き書式設定により、セルに色を
付けるようにしています。また、VBAでワークシート関数
を作りこみ、そのシートである色で塗りつぶされたセル
の数を数えるようにしています。
このとき、条件付き書式設定によりセルに色が付いてい
る箇所は、ワークシート関数によって塗りつぶしの色と
して認識されないようです。
ワークシート関数で「ある色」かどうかを判定するの
は、Interior.ColorIndexを使用しています。条件付き
書式設定によってセルの色が付いている箇所は、このプ
ロパティ値が-4142になっています(つまり、「塗りつぶ
しなし」とみなされている)。

条件付き書式設定で色が付けられる状態のまま、ワーク
シート関数で塗りつぶしの色が認識できるようにする方
法はあるのでしょうか? 分かりましたら教えてくださ
い。

投稿日時 - 2002-11-21 08:37:18

QNo.409736

困ってます

質問者が選んだベストアンサー

条件付き書式の色の数を数えようとする場合、どの条件が成立しているかを判定することが必要になります。
FormatConditionsのInterior.ColorIndexは設定した条件を示しているだけです。

単純な(1種類とか)条件付き書式の色個数カウントなら、コードに同じ条件を書きこんで集計できます。

下では、
  1.条件として、『セルの値が』、『数式が』の両方、
  2.条件の個数としては1~3個、
  3.多数種類の条件付き書式でのセルのパターン
の色を集計します。(複数の条件付き書式でも可のはずです。色々なパターンの合算です)
集計したいセル範囲(飛び離れていても可)を選択して下のマクロを実行します。

今の状態は、A、B列に結果を出力しています。
条件付き書式が設定されたセルのみを集計しており、最初の行に色無しの個数を表示しています。
不都合があれば出力位置を変更して下さい。(最後)


標準モジュールに貼り付けます。(Excel97で確認済みですが穴があるかも知れません。)

Sub fmtCnd_ColorsCount()
  Dim ttlArea As Range     '集計するセル範囲
  Dim rg As Range        'セル
  Dim RgVal As Variant     '評価するセル
  Dim cndFormula As String   '評価すべき式
  Dim cd As Integer       '条件付き書式のカウンタ
  Dim ColCot(56) As Integer   '集計結果
  Dim CotNashi As Integer    '色付きとして集計したか
  Dim cIdx As Integer      'カラーインデックス

  Set ttlArea = Selection
  For Each rg In ttlArea
    rg.Select
    CotNashi = -(Selection.FormatConditions.Count > 0)
    For cd = 1 To Selection.FormatConditions.Count
      With Selection.FormatConditions(cd)
        '条件が式の場合
        If .Type = xlExpression Then
          If Evaluate(.Formula1) Then
            cIdx = .Interior.ColorIndex
            ColCot(cIdx) = ColCot(cIdx) + 1: CotNashi = 0
          End If
        End If

        '条件が値の場合
        RgVal = Selection.Value
        If .Type = xlCellValue Then
          Select Case .Operator
            Case xlBetween    '次の値の間
              cndFormula = "AND(" & .Formula1 & "<=" & RgVal & "," _
                            & RgVal & "<=" & .Formula2 & ")"
            Case xlNotBetween  '次の値の間以外
              cndFormula = "not(AND(" & .Formula1 & "<=" & RgVal & "," _
                            & RgVal & "<=" & .Formula2 & "))"
            Case xlEqual     '次の値に等しい
              cndFormula = RgVal & "=" & .Formula1
            Case xlNotEqual   '次の値に等しくない
              cndFormula = "not(" & RgVal & "=" & .Formula1 & ")"
            Case xlGreater    '次の値より大きい
              cndFormula = RgVal & ">" & .Formula1
            Case xlLess     '次の値より小さい
              cndFormula = RgVal & "<" & .Formula1
            Case xlGreaterEqual '次の値以上
              cndFormula = RgVal & ">=" & .Formula1
            Case xlLessEqual   '次の値以下
              cndFormula = RgVal & "<=" & .Formula1
          End Select
          If Evaluate(cndFormula) Then
            cIdx = .Interior.ColorIndex
            ColCot(cIdx) = ColCot(cIdx) + 1: CotNashi = 0
          End If
        End If
      End With
    Next
    '条件付き書式で条件が成立せず色を付けなかった場合
    ColCot(0) = ColCot(0) + CotNashi
  Next
  '集計結果の出力
  Dim rw As Integer   '出力行カウンタ
  Cells(1, 1) = "カラーインデックス"  '2つ目の1がA列を表す
  Cells(1, 2) = "セルの数"    '2つ目の2がB列を表す
  For rw = 0 To 56
    If rw = 0 Then
      Cells(rw + 2, 1) = "色なし"
    Else
      Cells(rw + 2, 1) = "colorIndex:" & rw
    End If
    Cells(rw + 2, 2) = ColCot(rw)
  Next
End Sub

投稿日時 - 2002-11-21 16:25:20

お礼

ありがとうございます。やはり

>条件付き書式の色の数を数えようとする場合、どの条件
>が成立しているかを判定することが必要になります。
>FormatConditionsのInterior.ColorIndexは設定した条件
>を示しているだけです。

なのですね。教えてソースを少し改造して、なんとか満足
のいくものができました。
ちょっと気になったのは、条件付き書式設定で「式」に
した場合、式のセル参照が相対参照だと、Formula1プロ
パティに間違った内容が取得されてしまいました。
絶対参照ならいいのですが、書式コピーが使えないのが
面倒・・・。

投稿日時 - 2002-11-21 22:24:56

ANo.2

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.1

(1)ワークシート関数は「Application.WorksheetFunction.何々」というものを言っているのだと思いますが、エクセルで操作でセルに設定する関数(=何々)をVBAで使えるようにしたものだと思います。基本的にエクセル関数に存在しなければ、ワークシート関数に存在しないと考えるべきです。ただ関数にあり=ワークシート関数にありではなく、関数にあり(ほぼ等しい)ワークシート関数ようですが。
関数には条件付書式を捉える、設定するものはありません
ので、その方向の方法は諦めざるを得ません。CELL関数にもないようです。
(2)色で塗りつぶされたセルの数を数えるようにしています。---->
 せっかくVBAではセルの[値]でカウントする方法があるのですから、そちらにしたほうが(初等的プログラムではあっても)易しいと思います。
(3)「条件付き書式設定によってセルの色が付いている」セルの数をカウントするVBAコーディング例などほとんど載っている本もありません。操作で簡単、みんなやっていること、直ぐ習い始めた人でも思いつくことがVBAで簡単とは限りません。むしろ一番難しい(高等な技術や知識が要る)ことが多いです。自分の会得しているやさしい方法の領分に問題解法を引っ張り込んで解決するのが肝心です。下記も苦し紛れのやり方ですが、一応出来るというのを挙げます。
(4)
Sub test01()
For i = 1 To 9
If Cells(i, 1).FormatConditions.Count > 0 Then
If Cells(i, 1).FormatConditions(1). _
Interior.ColorIndex = 6 Then
  MsgBox i & "行は条件付書式セルの色は6"
  End If
End If
Next i
On Error Resume Next
End Sub
・簡単にするため、A列第9行までの領域を調べています。
 列を変える、複数列にする、最下行を捉える
 方法はわかりますよね。
・該当セルがあるとMsgboxで表示していますが、
 カウントに変える方法はできますよね。
 forループの外でt=0,ループの中でt=t+1
抜け出してtを利用する。
・色を他の色で聞く方法への変え方も判ると思います。

投稿日時 - 2002-11-21 10:08:07

あなたにオススメの質問