条件付書式で変更されたセルの色や文字の色を取得する
｜
｜
Excelの知識, マクロ / VBA, ユーザー定義関数 CountColor, DisplayFormat, ユーザー定義関数 ｜
指定したセルの色の件数を数える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は使えないので。
- セルに設定されている条件付書式の条件を引っ張ってくる
FormatConditionsの中にFormula1やらがある - VBA内で条件付書式の条件が発動するかどうか判定
- 判定の結果Trueだったら条件付書式で設定してあるセル色を取得
- そのセル色が指定のセル色かチェック、結果TrueならCount+1
こんな感じかなー。
１，２が面倒くさいんだよな。”セルの値が○○の場合”とかって簡単な条件であったらいいけど、”○○～△△の間”とか複数条件あったりすると、とかいろんなパターンを想定しておかないといけないので・・・。
ユーザー定義関数が発動しているかどうかのフラグがどっかに無いかなーって探したけど無さそう。
関連記事
- 前の記事
- グラフ内のテキストボックスを外に出せない現象
- 次の記事
- 自動入力候補って鬱陶しくないですか？
Comment
こんなものを作ってみました。宜しければご確認ください。
☆ 使い方
「=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