赤文字の入ったセル数をカウントしたい。

質問

社員勤務のシフト表カレンダーを、EXCEL2013で作ろうとしています（BOX型）。

　作ったカレンダーは、給与が20日締めですから、例えば、9月のカレンダーは、8月21日始まり、9月20日終了という形式です。 祝祭日と日曜日の日にちは、赤文字で表示してあります。

　次に、月間総労働時間を算出するために、該当のセル範囲（G20：S20）の中から、労働時間から除外すべき日数＝【『赤文字で表示した祝祭日と日曜日』が入った（セルの数）】を、カウントし、例えば、セルE26に、＝○○（G20：S20,3）の形式を用いて表示させたいと思います。VBAを使いたいのですがうまく作れません。ご教授下さい。

《整理》
1.セル範囲（G20：S20）中の月間総労働時間を算出したい。
2.労働時間から除外すべき祝祭日と日曜日は、赤文字で表記させている。
3.この赤文字の入ったセル数をカウントしたい。
4.表示形式は、＝○○（G20：S20,3）
5.VBAを使いたい。

《追記》
祝祭日と日曜日を赤文字に表記させた作業工程は、次の通りです。
(1)日曜日は、予めFontを赤に設定、(2)祝日は、「条件付き書式」を使いFontを赤に設定、(3)8月21日より、9月20日までの期間外の日にちは、Fontを白に設定し、見えないようにしました。

＜ 使用 Excel：Excel2013、使用 OS：Windows10 ＞

回答No.7

real beatin（@realbeatin）

No.6です。

> 想像するところ、解決できてない一因は、カレンダーを作成するに当たって、
> ＝DATE(M6,T6,Y6)-(WEEKDAY(DATE(M6,T6,Y6),1)-1)
> のような関数を利用しているため、計算過程を表示させると、
> Prange欄にシリアル値が表示されるようなることが
> 関係しているように思います。
"Prange欄"とは何でしょう？という疑問は残りますが、
その数式自体は、日付のx日前の日付を求める、という内容ですので、
戻り値がシリアル値、セルの表示形式が日付、
になるということでしたら、それは正常な動作ですね。
"解決できてない一因"として影響があるとは思えませんけれども。

> 残念ながら、今回は、未だ解決に至っておりません。
出来れば、条件付き書式のルールに設定した数式、を
提示して欲しかったのですけれども。
先に挙げたUDFのプロットを一段階だけ進めて、
数式の書き方に対する汎用性を高めてみましたので、
よければ、試してみて下さい。
（最初っから、こう書けばよかったのに、といった内容ですが）
具体的には、
数式内のセル参照を、相対参照として正しく書換える為に、
Application.ConvertFormulaメソッドに２回通しています。
論理値[TRUE|FALSE]を返す数式が正しく設定されていれば、
普通に使えるようになると思うのですが、、、。

> なお、日曜・祝祭日の合計値を、関数を利用して計算することは、自力でできますので、
> 取りあえず、そのように対処致します。
それが現実的な対応として十分なのであれば、
その方が安心でしょうね。
No.2にてtsubu-yukiさんご紹介のNETWORKDAYS.INTL()関数みたいな
無駄のないスッキリした数式が書けるように、
扱い易いシート設計を目指した方が良さそうにも思いますが、、、。

条件付き書式の設定内容について、一々再確認するのは面倒だけど、
条件付き書式のルールに設定した数式による判定を参照したい、
といったニーズに関する質問は、過去に数十件程度見たことがあります。
このうち、UDFで、というニーズは初めて見ました。
何故、UDFにする必要があるのか、という理由について、
今一度、ご自身で確認してみた方が総合的に吉なような気がします。
経験的を踏まえての想像ですが、
シート設計に難がある、といった場合である可能性が高いような、、、。

以下、改良版です。
シート上では、
　=CountDisplayFontColor(G20：S20,3)
のように数式を設定して使います。
' //
Function CountDisplayFontColor(Target As Range, ColorIdx)
Dim c As Range
Dim oFormatCondition As FormatCondition
Dim sFormula As String
Dim cn As Long
Dim flg As Boolean
　　For Each c In Target
　　　　flg = False
　　　　For Each oFormatCondition In c.FormatConditions
　　　　　　If oFormatCondition.Font.ColorIndex = ColorIdx Then
　　　　　　　　sFormula = oFormatCondition.Formula1
　　　　　　　　sFormula = Application.ConvertFormula( _
　　　　　　　　　　　　　　　　Formula:=sFormula, _
　　　　　　　　　　　　　　　　FromReferenceStyle:=xlA1, _
　　　　　　　　　　　　　　　　ToReferenceStyle:=xlR1C1, _
　　　　　　　　　　　　　　　　RelativeTo:=oFormatCondition.AppliesTo(1))
　　　　　　　　sFormula = Application.ConvertFormula( _
　　　　　　　　　　　　　　　　Formula:=sFormula, _
　　　　　　　　　　　　　　　　FromReferenceStyle:=xlR1C1, _
　　　　　　　　　　　　　　　　ToReferenceStyle:=xlA1, _
　　　　　　　　　　　　　　　　RelativeTo:=c)
　　　　　　　　If Evaluate(sFormula) = True Then
　　　　　　　　　　flg = True
　　　　　　　　　　Exit For
　　　　　　　　End If
　　　　　　End If
　　　　Next
　　　　If Not flg Then flg = c.Font.ColorIndex = ColorIdx
　　　　If flg Then cn = cn + 1
　　Next
　　CountDisplayFontColor = cn
End Function
' //