Excelで色付けしたセルに対し対応するセル値で返す方法

（画像参照）
横に名前、縦に日付の入った表で担当欄（C列）を埋めるVBA等の書き方が分からず困っています。

緑色に色付けしたセルが該当日の担当者を示しています。（4月1日高橋、2日佐藤、3日田中、4日高橋、5日鈴木、6日鈴木、7日田中）
担当欄（C列）に4行目の値（苗字）が返される（D5:G11の範囲のどこかのセルに緑色の塗りつぶしをするとC列に対応する苗字が表示される）ようにしたいのですが、関数やVBAでなにか解決策はありますでしょうか。

13行目は苗字ごとの緑セルの数をColorCountで数えたものです。
こちらの検索値（緑セル）を表の外につくりましたが（B2セル）、ほかに何かいい方法がありましたらそちらも教えて頂けると幸いです。
※colorcountの式は様々なサイトを参照して以下の様に書きましたが、それぞれどのように動いているのかは理解できていません。
Function Colorcount(R1 As Range, C As Range)
Dim r As Range
Application.Volatile
Colorcount = 0
For Each r In R1
If r.Interior.Color = C.Interior.Color Then
Colorcount = Colorcount + 1
End If
Next r
End Function

質問者からの補足コメント

• 

  説明不足で申し訳ございません。
  私自身VBAの知識は全くなく、設定している関数がどのように動いているか、どこがどう不自然なのかも見当がつきません。（通例の関数でしたら理解できます。）
  具体的にどのような関数をどのように並べて用いるべきか、VBAの書き方など例示していただけると非常に心強いです。
  業務の効率化のためなのですが、色付けでの判断は変更ができずこのような形になっております。
  厚かましいお願いにはなりますがよろしくお願い致します。

    補足日時：2020/11/13 09:37

No.6 回答者： ggrksdqn 回答日時： 2020/11/19 15:48

下の関数を試してみてください。

'[関数]
=GETSTAFFNAME(担当者リスト,塗りつぶしを探すセル範囲,色を指定するセル)
（例）=GETSTAFFNAME($C$4:$F$4,C5:F5,$B$2)

'[機能]
'指定した色で塗りつぶしたセルに対応する担当者名を返す

'[引数]
'R1 担当者の名前が入ったセル範囲
'R2 色付けしてあるセルを探すための対象になるセル範囲
'R3 色を指定するためのセル

Function GETSTAFFNAME(R1 As Range, R2 As Range, R3 As Range) As String

Application.Volatile

Dim r As Range

'対象のセル範囲に含まれるセルを一つ一つ調べる
For Each r In R2

'現在のセルの色が指定した色と同じ場合
If r.Interior.Color = R3.Interior.Color Then

'対応する担当者名を返す
'この関数 = セル（行番号, 列番号）.中身
GETSTAFFNAME = Cells(R1.Row, r.Column).Value

'処理を抜ける
Exit Function

End If

Next r '次のセルへ

End Function

No.5 回答者： googoo900 回答日時： 2020/11/14 22:41

色をつけたセルの位置を判別するのは、できるのかもしれませんが困難です。

（少なくとも私には無理）

色付けの代わりに何か文字を入力すればよいだけと思います。


自分ならこうするというのを、以下に紹介します。
セルの配置は質問文と同じとします。
添付図参照です。

◆手順
１．色付けされているセルに0(ゼロ)を入力します。
○(マル)とかでもよいのですが、漢数字や記号など類似のものがあるのでゼロがよいです。0なら全角で入力しても半角に修正されます。

２．C5セルに『=OFFSET(C5,MATCH("担当",C:C,0)-ROW(),MATCH(0,D5:G5,0))』を入力して、下方向に必要なところまでコピペします。

３．これで完成です。

※同じ日付に複数入力されていたら、最も左側に入力されているものになります。

No.4 回答者： fujillin 回答日時： 2020/11/13 10:05

No2です

No3様のご意見にまったく同感です。

＞私自身VBAの知識は全くなく、～～（通例の関数でしたら理解できます。）
であればなおさらのこと、関数で処理すれば宜しいのでは？？

関数での処理が不可能（あるいは難しい）内容であれば、VBAを利用することは妥当だと思いますが、どちらでもできる内容を（理解もできない）VBAで解決しようとすることには疑問です。

とは言え、どのような方法を採用するのかは、質問者様のご自由ではありますけれど・・・

No.3 回答者： ppp2122 回答日時： 2020/11/13 09:43

ハナシの腰を折るようですが

知識がないなら　ここで得た回答をそのままコピペし、
意図しない処理が行われていても気づかないケースがあるでしょう
サンプルコードを自分の環境に合わせて修正できるスキルが
最低限無いのであれば　やめたほうがいいと思います
特に　その結果を信用して物事が動くような場合
問題となると思います

そうなっても自身で対応出来ませんよね？

これは実際によく聞くケースです

No.2 回答者： fujillin 回答日時： 2020/11/12 15:51

こんにちは

VBAで処理をなさりたいなら、普通に処理をすれば、該当色のセルをカウントしたり、名前の表示はできるものと思います。

ただし、ご提示の関数ではVolatileに設定しているようですが、シート内のどこかのセルの値を変えないと再計算されませんので、表内のセル色を変えただけでは計算結果に即時反映はされないものと思います。
また、シート内の（表と関係のない）部分の入力に対して、毎回（同じことを）再計算しますので、効率が悪いとも言えます。


以下は、通常の関数のみでも簡単に処理できる、別の方法の一例です。

まず、色の付け方の仕組みを変えます。
（関数ではセルの色は調べられないため）
表内のセルに条件付き書式で、「値（例えば１）を入力したら、文字色、背景色ともに緑色」のような設定をしておきます。
（色を変えるよりも、値を入力・消去する方が操作も簡単ではないかと思います）

上記の仕組みにより、セルに１を入力すればご提示の図の様な表が表示できます。
13行目の集計は、単純にセル範囲の合計を取れば良いので、SUM関数やCOUNT関数で算出できます。
C列の担当者名は、セル位置をMATCH関数やSUMPRODUCT等で調べられますので、これを利用してINDEX関数で算出が可能です。

No.1 回答者： ppp2122 回答日時： 2020/11/12 15:12

セルを１つづつ調べていき

緑色だったらカウントを１つ繰り上げる
というコードですが
カウントの結果を表示するものが無いので
これを実行しても何も起きません
また、無用なセルを調べるため　無駄な時間がかかります
調べる範囲は限定すべきです

また、色で判定はあまりいい方法ではありません
入力者が　色付けをする場合
緑色でも　１つではなく　いっぱいあり
誤判定に繋がるからです