Help with populating rota based on colour of cell

[Nashpotatoes]

Hello, I am using a well established rota on excel but wishing to make some things automated using VBA to save time.
It is a rolling rota where employees have different roles (on a 6 week rolling rota) but also cross cover for absences/annual leave etc, within this there is also a 'jobs rota' for 3 of the specific roles. Currently the roles are based on cell colour on the rota (and I cannot change this)
One thing that takes time is updating the roles of these 3 employees based on the separate 'jobs rota'.
What I wish to to is add the jobs from the 'jobs rota' to only specific coloured cells on the main rota, but also not delete what is already written in these cells (such as 'long day') only add to it (eg. to say 'Long day - Lab' or 'Long day Pick-up').

...Sorry if this is not explained very well, please see mini-sheet below

Thank you in advance for any help you may be able to offer

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Main rota										Jobs rota for red, yellow, blue
2				People								Week 1	Week 2	Week 3	Week 4
3	Week 1-4	Date	Day	1	2	3	4	5	6		Monday	Lab	Office	Floor	Outside
4	1	01/08/2022	Monday				Off					Collection	Study	Study	Study
5		02/08/2022	Tuesday			Long day	Off					Pick up	Pick up	Pick up	Pick up
6		03/08/2022	Wednesday		Off						Tuesday	Study	Office	Theatre	Office
7		04/08/2022	Thursday		Off							Clinic 1	Clinic 2	Clinic 3	Clinic 4
8		05/08/2022	Friday		Off							Study		Study
9		06/08/2022	Saturday								Wednesday
10		07/08/2022	Sunday
11	2	08/08/2022	Monday										Theatre		Theatre
12		09/08/2022	Tuesday				Long day				Thursday	Clinic 5	Clinic 6	Clinic 7	Clinic 8
13		10/08/2022	Wednesday	Long day				Off				Pick-up	Clinic 8	Pick -up	Clinic 6
14		11/08/2022	Thursday			Long day		Off				Theatre		Theatre
15		12/08/2022	Friday					Off			Friday	Clinc 9
16		13/08/2022	Saturday										Lab	Clinic 9	Lab
17		14/08/2022	Sunday										Study		Study
18	3	15/08/2022	Monday
19		16/08/2022	Tuesday				Long day
20		17/08/2022	Wednesday			Off		Long day
21		18/08/2022	Thursday			Off
22		19/08/2022	Friday
23		20/08/2022	Saturday
24		21/08/2022	Sunday
25	4	22/08/2022	Monday
26		23/08/2022	Tuesday
27		24/08/2022	Wednesday
28		25/08/2022	Thursday
29		26/08/2022	Friday
30		27/08/2022	Saturday
31		28/08/2022	Sunday
32	1	29/08/2022	Monday
33		30/08/2022	Tuesday
34		31/08/2022	Wednesday
35		01/09/2022	Thursday
36		02/09/2022	Friday
37
38
Sheet1