【ExcelVBA】条件付き書式はワークシート関数では使えない
久々にはまったので自分用にメモ。条件付き書式の設定されたセルで、表示された色が何色かを把握するにはDisplayFormatを使いますが、DisplayFormatはユーザ定義関数では使えません。
やりたかったこと
こういうワークシートがありまして
このうち、日曜日は条件付き書式で色付けしてあります。
この条件付き書式で赤くなっているセルと、手動で赤くしたセルと合わせてカウントするようなユーザ定義関数を作ったところ、エラーとなりました。
Public Function CountRed(Rng As Range)
Dim R As Range
Dim Cnt As Long
Cnt = 0
For Each R In Rng
If R.Value <> "" Then
If RGB(255, 0, 0) = R.Font.Color Or RGB(255, 0, 0) = R.DisplayFormat.Font.Color Then
Cnt = Cnt + 1
End If
End If
Next R
CountRed = Cnt
End Function
なんで？
原因
理由は、DisplayFormatがユーザ定義関数では機能しないからです。そういう仕様です。
Range.DisplayFormat プロパティ (Excel)
https://learn.microsoft.com/ja-jp/office/vba/api/excel.range.displayformat
↑これを読むと、「 ただし、DisplayFormat は、Visual Basic for Applications (VBA) から呼び出される関数では機能します。 」とありました。確かに、イミディエイトペインでは機能します。
回避策
そこで仕方がないのでボタンを一個ワークシート上に作成し、そのボタンを押したら特定のセルに値が反映されるようにしました。
Public Sub SetCountRed()
ThisWorkbook.Worksheets(1).Range("AC4").Value _
= CountRed(ThisWorkbook.Worksheets(1).Range("B4:Z8"))
End Sub
かっこ悪いですけど仕方がないです。
