条件付書式で変更されたセルの色や文字の色を取得する

指定したセルの色の件数を数えるCountColorというユーザー定義関数が、当ブログの人気記事なわけですが、あれって条件付書式で変更されたセルの色は判断出来ないんですよね。

元々のセルの色しか取得出来てなくて、条件付書式が発動後の色を取得する方法が無いか試行錯誤した結果、
DisplayFormatオブジェクトがあったんだけど、これってユーザー定義関数で動かなくて・・・。

注釈

DisplayFormat プロパティはユーザー定義関数では機能しないことに注意してください。たとえば、セル内部の色を返すワークシート関数で、Range(n).DisplayFormat.Interior.ColorIndex のような行を使用したとします。このワークシート関数が実行したときに #VALUE! エラーが返されます。

または、特定の範囲の設定を返すワークシート関数では DisplayFormat プロパティを使用できません。ただし、DisplayFormat は、Visual Basic for Applications (VBA) から呼び出される関数では機能します。

(Microsoft公式より https://msdn.microsoft.com/ja-jp/library/office/ff838814.aspx)

ってことで困った。

DisplayFormat

DisplayFormatは条件付書式が発動後のセル色など情報を取得できるオブジェクト。おお!って思ったけど上記の注釈で萎え。

以下のようにマクロ走らせてカウント結果をMsgBoxで出す事は可能だけど。。。

Sub test()
    Dim R As Range
    i = 0
    Set R = Range("A1:B2") 'チェックする範囲を指定
    Set C = Range("C1") '条件色セルを指定
    For y = 1 To R.Columns.Count
        For x = 1 To R.Rows.Count
            If R(x, y).DisplayFormat.Interior.ColorIndex = C.Interior.ColorIndex Then i = i + 1
        Next
    Next
    MsgBox ("一致セル数 : " & i)
End Sub

 

ユーザー定義関数でやろうと思ったら考えられる方法はこうかな?

DisplayFormatは使えないので。

 

  1. セルに設定されている条件付書式の条件を引っ張ってくる
    FormatConditionsの中にFormula1やらがある
  2. VBA内で条件付書式の条件が発動するかどうか判定
  3. 判定の結果Trueだったら条件付書式で設定してあるセル色を取得
  4. そのセル色が指定のセル色かチェック、結果TrueならCount+1

 

こんな感じかなー。
1,2が面倒くさいんだよな。”セルの値が○○の場合”とかって簡単な条件であったらいいけど、”○○~△△の間”とか複数条件あったりすると、とかいろんなパターンを想定しておかないといけないので・・・。

ユーザー定義関数が発動しているかどうかのフラグがどっかに無いかなーって探したけど無さそう。

Comment

  1. 吉川 真一 より:

    こんなものを作ってみました。宜しければご確認ください。

    ☆ 使い方
    「=TargetCount(“田中”,255,B1:F10)」のような感じです。

    Function TargetCount(対象文字 As String, 対象色 As Long, 対象範囲 As Range) As Variant

    Dim 位置 As Long
    Dim 対象 As Range
    Dim セル As Range
    Dim 対象セル As FormatCondition

     For 位置 = 1 To 対象範囲.Cells.Count
      If InStr(対象範囲(位置).Value, 対象文字) > 0 Then
       If 対象範囲(位置).Interior.Color = 対象色 Then
        TargetCount = TargetCount + 1
       Else
        Set 対象 = Cells(対象範囲(位置).Row, 対象範囲(位置).Column)
        For Each 対象セル In 対象.FormatConditions
         If IsNumeric(対象セル.Interior.Color) Then
          If 対象色 = 対象セル.Interior.Color Then
           TargetCount = TargetCount + 1
          End If
         End If
        Next
       End If
      End If
     Next

    End Function

コメントをどうぞ

メールアドレスが公開されることはありません。

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

  • ▼お小遣い稼ぎしませんか?▼ 日々の生活にhappyをプラスする|ハピタス

    クレジットカード・FX口座を作るだけで簡単に3万円GETです。一切支出はありません。

置換 TODAY 作業効率UP 図形 SUM 日付 INDEX HLOOKUP SUMIFS ユーザー定義関数 グラフ COUNTIF フロー図 DATE 無料 検索 印刷 ワイルドカード 条件付き書式 バーコード 目標 変換 テンプレート COLUMN NA() VLOOKUP XLSTART SUMIF MATCH コピペ
  • 関西在住の30代サラリーマン。事務職で毎日Excelと戯れています。
    システム導入なんて資金が無い!Excelでなんとかやりたい零細中小の社長さん・事務員さんの力になればと思います。
    詳しいプロフィール


PAGE TOP ↑