従業員増減対応で当番種類の増減対応な当番表

VBAでタイトルのような当番表を作りたいと考えています。
こちらにある質問やご回答を参考にいろいろ試しましたが、
なにぶんVBAはド素人のためお手上げ状態です。よろしくお願いします<(_ _;)>

カタチとしては以下のような感じには出来ていますが、
しかし土日の休日や、祝日リストから参照しての休日が表示されるように関数を使用してしまうと、例えば、希望休や有休などを表に記入すると関数が消えてしまい、表内に関数を入れられません。

ならば希望休(有給含む)は手入力で先に入力して、それを条件付き書式で識別できるように色付けて、そして土日と祝祭日も条件付き書式で背景色を付ければ、ほら、休みがどこか分かりやすい！
と思ったのですが。

色のついていないセル(休みではないセル)にだけ当番を割り当てるってできるの？
てか色ってどうやって認識させられるんだろ？ってところで困っています。

そこでなにか方法があれば助けていただきたいです。

■sheetは２つあって、「祝祭休日」「当番表」とあります。

①sheet「祝祭休日」について
自動計算(関数や計算)で算出された祝祭休日の年間の年月日をリストしています。
・A1～A54=祝祭休日の分類、B1=当番表!A1、C1="祝祭日"、D1="備考"
・B2～B37=各祝休日の年月日、B38～B45=会社指定休日(ここは毎年変るので手入力)、
　B46～B54=各イベント(クリスマスやバレンタインなど)の年月日、と分けてます。
・C2～C54=各祝祭休日の名称を記入してます。
・D2～D54=備考を書いています。
※ちなみに、こちらはこれで完成でいいかなぁと思っています。

②sheet「当番表」について
・A1=年(今なら"2022"を手入力)、A2=月(今なら"7"を手入力)、
・B1=DATE(A1,A2,1)、C1～AF1=左のセル+1
・B2～AF2=sheet祝祭休日から祝祭日の名称をXlookuoで引用
・B3～AF3=Weekday関数とaaaで曜日表示　※sheet祝祭休日から条件付き書式で色が付くようにしています
・A3="氏名"、A4～A14="各従業員の氏名を手入力"しています。　
　※今は(仮)として10名(A14)にしていますが、部署によっては多いところもあり、
　　また入社があれば増え、反対に減ることもあるため、表は26名対応(A29)にしてあります。
・B4:AF29=当番表　
　※ここでは、希望休を"休"、有休を"有"と手入力したいと考えていて、
　条件付き書式でそれぞれ色が付きます。また、sheet祝祭休日に応じて条件付き書式で
　「土、日、祝、指定休」の列に色が付くようになってます。
・AG3="合計"、AG4～AG29=各当番の合計
・AH3～AK3="各当番名"　※例)AH3=燃、AI=プラ、AJ=段、AK=消毒
　※部署によては当番が４～６項目ある場合もあって、当番の種類は固定されたものではありません。
・AH4～AH29=各従業員ごとに"燃"の合計、AI4～AI29は"プラ"の合計、
　AJ4～AJ29は"段"の合計、AK4～AK29は"消毒"の合計・・・と、各従業員の行ごとに各当番の合計が続き、
　つまり、この合計をAG4～AG29で各当番を合計しています。

■完成希望
各従業員の休み(希望休、有休)を土日祝の休みを考慮し、また、増減する人員に対応し、
ランダムに割り振られ、かつ月間で合計回数と各当番回数も平等に振り分けられるようにしたいと思っています。
※Aさんは毎月第一週の月曜日は固定というものではなく、
　毎月何の担当するかはバラバラ、されど月合計ではみんな平等というような。

また、ややこしいのですが、当番の種類が部署によって異なるということと、
当番によっては「週に何回やるのか」「何人でするのか」も違っていて、
そういった変更にも対応できる、今後も使用できるものを想定しています。

ちなみに今作っている表では、以下のようにしたいと考えています。
　
・燃＝週１回、４人
・プラ＝週１回、１人
・段＝週１回、１人
・消毒＝毎日、１人

何かいい方法がありますでしょうか？
お手数をおかけしますがお力貸していただけないでしょうか。
よろしくお願いいたします。

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

• １，に関して
  ケース①の「同じ日に４人を割り当て」です。
  ※その他「週何回、何人でするか」という設定が出来るならば、「何人でするか」の部分は全て同じ日に割り当てとなります。
  
  ２，に関して
  ②の「(イベント含む)」という部分のみ補足いたします。
  sheet祝祭休日にイベントの項目があり、sheet当番表に反映され、「今日は何の日か」というところでB2～AF2上でクリスマスやバレンタインデー、ホワイトデーと表示されます(もちろん他祝日も表示されます)。ですが、こういったクリスマスなどは祭日ではあっても国民休日には当たらないため仕事となります。そのため、②に関しては下記以外となります。
  
  ②シート：「祝祭休日」のB2～B54の日付（イベント除く）
  ※つまり「祝日(元旦～勤労感謝の日)、その他(お盆や大晦日)、会社指定休日」が仕事休み
  　　「イベント(バレンタインやひな祭り、クリスマス)」は仕事

  No.1の回答に寄せられた補足コメントです。 補足日時：2022/07/19 19:30

• ３，に関して
  現時点で設定してるのは４項目(燃、プラ、段、消毒)ですが、部署によっては６項目ある部署もあるため、増えて6項目になっても対応できるものを望んでいます。
  （例）現時点で「AH3～AK3」に「燃、プラ、段、消毒」と入っていますが、「AL＝トイレ」や「AM＝休憩室」と項目が追加されたり、あるいは減っても、週何回で何人でするかという設定をすると反映される、というようなものです。
  
  ４，に関して
  マクロではなく関数式で算出するように関数式を入れてあります。
  
  ５，に関して
  sheet当番表のA1に入れている「年(2022年)」をsheet祝祭休日のB1に引用しており、そのB1を基としてB2～B54に、それぞれの祝祭日に合った計算で日にち(シリアル値)が算出されるようになっていて、そのシリアル値をユーザー定義から「m/d」としています。
  
  ６，に関して
  そのご理解であっております。

    補足日時：2022/07/19 19:34

• sheet当番表

  
    補足日時：2022/07/20 06:23

• sheet祝祭休日

  
    補足日時：2022/07/20 06:24

• 旧当番表

  
    補足日時：2022/07/20 06:25

• 以下、お役に立てば...
  
  マクロは組んでいませんが、必要ヵ所に関数式が入っていると思います。条件付書式や関数式、その他ご確認いただき活用いただければと思います。
  
  『～Excel19Ver』
  https://ac-data.info/d/FntBHuurcDY
  sheet当番表のB2～AF2の参照時、Xlookupに対応していないためINDEX、MATCHで参照してます。
  
  『Excel21～365Ver』
  https://ac-data.info/d/5AyNzBsij2o
  こちらはXlookupで参照してます。どちらかお使いください。
  
  ※sheet当番表のAG1に歴日数「=TEXT(DAY(EOMONTH(B1,0)),0)」、A30に記入されている従業員数「=SUBTOTAL(103,A4:A29)」がありますが、何かお役に立てれば…と置いてますが、必要なければ消して構いません。

    補足日時：2022/07/21 00:52

No.1 回答者： tatsumaru77 回答日時： 2022/07/19 11:42

補足要求です。

１．燃＝週１回、４人となっていますが、同じ日に４人を割り当てるのでしょうか。それとも、別々の日に４人を割り当てるのでしょうか。
ケース①　7/4 燃　Aさん、Bさん、Cさん、Dさん
ケース②　7/4 燃　Aさん、7/5 燃　Bさん、7/6 燃　Cさん、7/7 燃　Dさん

ケース①、ケース②のどちらでしょうか。

２．割り当てるのは、出勤日のみと理解して良いですか。
出勤日は、下記以外の日とする
①土、日、
②シート：「祝祭休日」のB2～B54の日付（イベント含む）
③当番表に予め記入された日（有、休）

３．割り当てた日については、提示例では、下記のいづれかの文字が設定されると理解してよいですか。
燃、プラ、段、消毒

４．AG4：AK29の合計は、マクロではなく、関数式で算出するようになっていると理解してよいですか。（マクロでは関知しない）

５．シート：「祝祭休日」のB2～B54の日付は、具体的にはどのように記入されていますか。シリアル日付ではないと考えています。
例　1月1日　（文字列）
　　1/1 （文字列）
等が考えられます。

６．週１回割り当ての場合、先月分を考慮する必要があると考えていますが、それであってますか。
例　段を6/30に割り当て済みなら、7/1の段は割り当てない。
　　段を6/27～6/30まで割り当て無なら、7/1に段を割り当てる。