VBA using INDEX and MATCH based on cell colour

[Nashpotatoes]

I am trying to populate a rota of myself and colleagues (where unfortunately roles are denoted by colour of cell) and automatically update sub-roles/jobs based on a separate 'jobs-rota'

Cross-posting: I have posted previously here - Help with populating rota based on colour of cell and have tried to solve issue but keep coming across problems

Currently I have managed to achieve what I want, but only for the red cells (I believe this is due to the way that MATCH and INDEX functions work by finding the first cell that meets the criteria - and this happens to be a red cell)

See below for the VBA code I am using currently...

Sub populaterota()
Dim c As Range
Dim r As Long
For Each c In Selection
If c.Interior.Color = RGB(255, 0, 0) Then c.Value = c.Value & " " & (Application.WorksheetFunction.Index(Sheet2.Range("B3:E17"), Application.WorksheetFunction.Match(Sheet1.Range("D" & c.Row), Sheet2.Range("A3:A17"), 0), Application.WorksheetFunction.Match(Sheet1.Range("B" & c.Row), Sheet2.Range("B2:E2"), 0)))
If c.Interior.Color = RGB(255, 255, 0) Then c.Value = c.Value & " " & (Application.WorksheetFunction.Index(Sheet2.Range("B3:E17"), Application.WorksheetFunction.Match(Sheet1.Range("D" & c.Row), Sheet2.Range("A3:A17"), 0), Application.WorksheetFunction.Match(Sheet1.Range("B" & c.Row), Sheet2.Range("B2:E2"), 0)))
If c.Interior.Color = RGB(68, 114, 196) Then c.Value = c.Value & " " & (Application.WorksheetFunction.Index(Sheet2.Range("B3:E17"), Application.WorksheetFunction.Match(Sheet1.Range("D" & c.Row), Sheet2.Range("A3:A17"), 0), Application.WorksheetFunction.Match(Sheet1.Range("B" & c.Row), Sheet2.Range("B2:E2"), 0)))
Next
End Sub

Please see below for Xl2bb

What I am trying to do is populate the main rota on sheet 1 with the jobs on sheet2 - matching both week number (1-4) and day of week, but only for the defined cell colours on the main rota

Sheet1:

Book1 - VBA practice.xlsm
	A	B	C	D	E	F	G	H	I	J
1					Main rota
2					People
3	Week 1-4		Date	Day	1	2	3	4	5	6
4	1	1	01/08/2022	Monday				Off
5		1	02/08/2022	Tuesday			Long day	Off
6		1	03/08/2022	Wednesday		Off
7		1	04/08/2022	Thursday		Off
8		1	05/08/2022	Friday		Off
9		1	06/08/2022	Saturday
10		1	07/08/2022	Sunday
11	2	2	08/08/2022	Monday
12		2	09/08/2022	Tuesday				Long day
13		2	10/08/2022	Wednesday	Long day				Off
14		2	11/08/2022	Thursday			Long day		Off
15		2	12/08/2022	Friday					Off
16		2	13/08/2022	Saturday
17		2	14/08/2022	Sunday
18	3	3	15/08/2022	Monday
19		3	16/08/2022	Tuesday				OC
20		3	17/08/2022	Wednesday			Off		Long day
21		3	18/08/2022	Thursday			Off
22		3	19/08/2022	Friday
23		3	20/08/2022	Saturday
24		3	21/08/2022	Sunday
25	4	4	22/08/2022	Monday
26		4	23/08/2022	Tuesday
27		4	24/08/2022	Wednesday
28		4	25/08/2022	Thursday
29		4	26/08/2022	Friday
30		4	27/08/2022	Saturday
31		4	28/08/2022	Sunday
32	1	1	29/08/2022	Monday
33		1	30/08/2022	Tuesday
34		1	31/08/2022	Wednesday
35		1	01/09/2022	Thursday		OC
36		1	02/09/2022	Friday
Sheet1

Sheet2:

Book1 - VBA practice.xlsm
	A	B	C	D	E
1	Jobs rota for red, yellow, blue
2		1	2	3	4
3	Monday	Lab	Office	Floor	Outside
4	Monday	Collection	Study	Study	Study
5	Monday	Pick up	Pick up	Pick up	Pick up
6	Tuesday	Study	Office	Theatre	Office
7	Tuesday	Clinic 1	Clinic 2	Clinic 3	Clinic 4
8	Tuesday	Study		Study
9	Wednesday
10	Wednesday
11	Wednesday		Theatre		Theatre
12	Thursday	Clinic 5	Clinic 6	Clinic 7	Clinic 8
13	Thursday	Pick-up	Clinic 8	Pick -up	Clinic 6
14	Thursday	Theatre		Theatre
15	Friday	Clinic 9
16	Friday		Lab	Clinic 9	Lab
17	Friday		Study		Study
Sheet2

I would be grateful for any help or suggestions. I am interested in VBA (of which I am definitely a novice) and looking to continue to learn so explanations also very much appreciated. Thank you