Fill cells with colour based on another cell colour

[Manexcel]

I am using Windows 10 with Excel 2019.
1) I have a number of groups that meet at irregular periods throughout the year. This is outlined in the table below.
2) I have calculated the future the dates for each group meeting based on their meeting dates (Cols E thru to P)
3) Using conditional formatting I have highlighted, in say yellow, the meeting dates from 'today' +30 days - E19 and E20
4) Cols Q to S just control the month number, Month and day number in the week to calculate dates (based on the best formula I could find)

SS Meetings Example v0.4.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
6	Group Name	Meeting on	Day	Week	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030	2031	2032	Month No.	Occurance in Month	Day No. in Week
7	Group 1	3rd Tue in January	Tue	3	19/01/2021	18/01/2022	17/01/2023	16/01/2024	21/01/2025	20/01/2026	19/01/2027	18/01/2028	16/01/2029	15/01/2030	21/01/2031	20/01/2032	1	3	3
8	Group 2	1st Thur in February	Thu	1	04/02/2021	03/02/2022	02/02/2023	01/02/2024	06/02/2025	05/02/2026	04/02/2027	03/02/2028	01/02/2029	07/02/2030	06/02/2031	05/02/2032	2	1	5
9	Group 3	3rd Tue in February	Tue	3	16/02/2021	15/02/2022	21/02/2023	20/02/2024	18/02/2025	17/02/2026	16/02/2027	15/02/2028	20/02/2029	19/02/2030	18/02/2031	17/02/2032	2	3	3
10	Group 4	4th Wed in February	Wed	4	24/02/2021	23/02/2022	22/02/2023	28/02/2024	26/02/2025	25/02/2026	24/02/2027	23/02/2028	28/02/2029	27/02/2030	26/02/2031	25/02/2032	2	4	4
11	Group 1	1st Mon in March	Mon	1	01/03/2021	07/03/2022	06/03/2023	04/03/2024	03/03/2025	02/03/2026	01/03/2027	06/03/2028	05/03/2029	04/03/2030	03/03/2031	01/03/2032	3	1	2
12	Group 2	1st Thur in March	Thu	1	04/03/2021	03/03/2022	02/03/2023	07/03/2024	06/03/2025	05/03/2026	04/03/2027	02/03/2028	01/03/2029	07/03/2030	06/03/2031	04/03/2032	3	1	5
13	Group 3	3rd Tue in March	Tue	3	16/03/2021	15/03/2022	21/03/2023	19/03/2024	18/03/2025	17/03/2026	16/03/2027	21/03/2028	20/03/2029	19/03/2030	18/03/2031	16/03/2032	3	3	3
14	Group 4	2nd Thur April	Thu	2	08/04/2021	14/04/2022	13/04/2023	11/04/2024	10/04/2025	09/04/2026	08/04/2027	13/04/2028	12/04/2029	11/04/2030	10/04/2031	08/04/2032	4	2	5
15	Group 1	3rd Thu in April	Thu	3	15/04/2021	21/04/2022	20/04/2023	18/04/2024	17/04/2025	16/04/2026	15/04/2027	20/04/2028	19/04/2029	18/04/2030	17/04/2031	15/04/2032	4	3	5
16	Group 2	4th Wed in April	Wed	4	28/04/2021	27/04/2022	26/04/2023	24/04/2024	23/04/2025	22/04/2026	28/04/2027	26/04/2028	25/04/2029	24/04/2030	23/04/2031	28/04/2032	4	4	4
17	Group 3	3rd Tue in May	Tue	3	18/05/2021	17/05/2022	16/05/2023	21/05/2024	20/05/2025	19/05/2026	18/05/2027	16/05/2028	15/05/2029	21/05/2030	20/05/2031	18/05/2032	5	3	3
18	Group 4	1st Monday in June	Mon	1	07/06/2021	06/06/2022	05/06/2023	03/06/2024	02/06/2025	01/06/2026	07/06/2027	05/06/2028	04/06/2029	03/06/2030	02/06/2031	07/06/2032	6	1	2
19	Group 1	2nd Mon in June	Mon	2	14/06/2021	13/06/2022	12/06/2023	10/06/2024	09/06/2025	08/06/2026	14/06/2027	12/06/2028	11/06/2029	10/06/2030	09/06/2031	14/06/2032	6	2	2
20	Group 2	4th Thur in June	Thu	4	24/06/2021	23/06/2022	22/06/2023	27/06/2024	26/06/2025	25/06/2026	24/06/2027	22/06/2028	28/06/2029	27/06/2030	26/06/2031	24/06/2032	6	4	5
21	Group 3	4th Mon in July	Mon	4	26/07/2021	25/07/2022	24/07/2023	22/07/2024	28/07/2025	27/07/2026	26/07/2027	24/07/2028	23/07/2029	22/07/2030	28/07/2031	26/07/2032	7	4	2
22	Group 4	3rd Tue in September	Tue	3	21/09/2021	20/09/2022	19/09/2023	17/09/2024	16/09/2025	15/09/2026	21/09/2027	19/09/2028	18/09/2029	17/09/2030	16/09/2031	21/09/2032	9	3	3
23	Group 1	1st Thur in September	Thu	4	23/09/2021	22/09/2022	28/09/2023	26/09/2024	25/09/2025	24/09/2026	23/09/2027	28/09/2028	27/09/2029	26/09/2030	25/09/2031	23/09/2032	9	4	5
24	Group 2	4th Thur in September	Thu	4	23/09/2021	22/09/2022	28/09/2023	26/09/2024	25/09/2025	24/09/2026	23/09/2027	28/09/2028	27/09/2029	26/09/2030	25/09/2031	23/09/2032	9	4	5
25	Group 3	1st Thur in October	Thu	1	07/10/2021	06/10/2022	05/10/2023	03/10/2024	02/10/2025	01/10/2026	07/10/2027	05/10/2028	04/10/2029	03/10/2030	02/10/2031	07/10/2032	10	1	5
26	Group 4	4th Wed in October	Wed	4	27/10/2021	26/10/2022	25/10/2023	23/10/2024	22/10/2025	28/10/2026	27/10/2027	25/10/2028	24/10/2029	23/10/2030	22/10/2031	27/10/2032	10	4	4
27	Group 1	1st Wed in December	Wed	1	01/12/2021	07/12/2022	06/12/2023	04/12/2024	03/12/2025	02/12/2026	01/12/2027	06/12/2028	05/12/2029	04/12/2030	03/12/2031	01/12/2032	12	1	4
28	Group 2	2nd Wed in December	Wed	2	08/12/2021	14/12/2022	13/12/2023	11/12/2024	10/12/2025	09/12/2026	08/12/2027	13/12/2028	12/12/2029	11/12/2030	10/12/2031	08/12/2032	12	2	4
Sheet1

Cell Formulas
Range	Formula
C7:C28	C7	=TEXT(WEEKDAY(E7), "ddd")
D7:D28	D7	=INT((DAY(E7)-1)/7)+1
E7:P28	E7	=DATE(E$6,$Q7,1+7*$R7)-WEEKDAY(DATE(E$6,$Q7,8-$S7))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E7:P29,E32:P32	Expression	=AND(E7>TODAY(),E7<=(TODAY()+30))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
Q7:Q29	List	1,2,3,4,5,6,7,8,9,10,11,12
R7:R29	List	1,2,3,4,5
S7:S29	List	1,2,3,4,5,6,7

What I would like to happen
a) For each date(s) that are highlighted in, say yellow, for their respective group name/meeting on/day/week to also be highlighted in yellow.
b) Obviously, and going forward, when 'todays' date +30 days, in Col E to P (eventually changes the respective rows in columns A thru D will also change

Example 1: So if "todays" date +30 (days) is 01/01/2022 then A37 to D37 and F37 cells should all be yellow only.
Example 2: So if "todays" date +30 (days) is 01/03/2023 then A41 to D41 and G41 to G43 cells will all be yellow only.
and so on...

SS Meetings Example v0.4.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
36	Group Name	Meeting on	Day	Week	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030	2031	2032	Month No.	Occurance in Month	Day No. in Week
37	Group 1 (example 1)	3rd Tue in January	Tue	3	19/01/2021	18/01/2022	17/01/2023	16/01/2024	21/01/2025	20/01/2026	19/01/2027	18/01/2028	16/01/2029	15/01/2030	21/01/2031	20/01/2032	1	3	3
38	Group 2	1st Thur in February	Thu	1	04/02/2021	03/02/2022	02/02/2023	01/02/2024	06/02/2025	05/02/2026	04/02/2027	03/02/2028	01/02/2029	07/02/2030	06/02/2031	05/02/2032	2	1	5
39	Group 3	3rd Tue in February	Tue	3	16/02/2021	15/02/2022	21/02/2023	20/02/2024	18/02/2025	17/02/2026	16/02/2027	15/02/2028	20/02/2029	19/02/2030	18/02/2031	17/02/2032	2	3	3
40	Group 4	4th Wed in February	Wed	4	24/02/2021	23/02/2022	22/02/2023	28/02/2024	26/02/2025	25/02/2026	24/02/2027	23/02/2028	28/02/2029	27/02/2030	26/02/2031	25/02/2032	2	4	4
41	Group 1 (example 2)	1st Mon in March	Mon	1	01/03/2021	07/03/2022	06/03/2023	04/03/2024	03/03/2025	02/03/2026	01/03/2027	06/03/2028	05/03/2029	04/03/2030	03/03/2031	01/03/2032	3	1	2
42	Group 2 (example 2)	1st Thur in March	Thu	1	04/03/2021	03/03/2022	02/03/2023	07/03/2024	06/03/2025	05/03/2026	04/03/2027	02/03/2028	01/03/2029	07/03/2030	06/03/2031	04/03/2032	3	1	5
43	Group 3 (example 2)	3rd Tue in March	Tue	3	16/03/2021	15/03/2022	21/03/2023	19/03/2024	18/03/2025	17/03/2026	16/03/2027	21/03/2028	20/03/2029	19/03/2030	18/03/2031	16/03/2032	3	3	3
44	Group 4	2nd Thur April	Thu	2	08/04/2021	14/04/2022	13/04/2023	11/04/2024	10/04/2025	09/04/2026	08/04/2027	13/04/2028	12/04/2029	11/04/2030	10/04/2031	08/04/2032	4	2	5
45	Group 1	3rd Thu in April	Thu	3	15/04/2021	21/04/2022	20/04/2023	18/04/2024	17/04/2025	16/04/2026	15/04/2027	20/04/2028	19/04/2029	18/04/2030	17/04/2031	15/04/2032	4	3	5
46	Group 2	4th Wed in April	Wed	4	28/04/2021	27/04/2022	26/04/2023	24/04/2024	23/04/2025	22/04/2026	28/04/2027	26/04/2028	25/04/2029	24/04/2030	23/04/2031	28/04/2032	4	4	4
47	Group 3	3rd Tue in May	Tue	3	18/05/2021	17/05/2022	16/05/2023	21/05/2024	20/05/2025	19/05/2026	18/05/2027	16/05/2028	15/05/2029	21/05/2030	20/05/2031	18/05/2032	5	3	3
48	Group 4	1st Monday in June	Mon	1	07/06/2021	06/06/2022	05/06/2023	03/06/2024	02/06/2025	01/06/2026	07/06/2027	05/06/2028	04/06/2029	03/06/2030	02/06/2031	07/06/2032	6	1	2
49	Group 1	2nd Mon in June	Mon	2	14/06/2021	13/06/2022	12/06/2023	10/06/2024	09/06/2025	08/06/2026	14/06/2027	12/06/2028	11/06/2029	10/06/2030	09/06/2031	14/06/2032	6	2	2
50	Group 2	4th Thur in June	Thu	4	24/06/2021	23/06/2022	22/06/2023	27/06/2024	26/06/2025	25/06/2026	24/06/2027	22/06/2028	28/06/2029	27/06/2030	26/06/2031	24/06/2032	6	4	5
51	Group 3	4th Mon in July	Mon	4	26/07/2021	25/07/2022	24/07/2023	22/07/2024	28/07/2025	27/07/2026	26/07/2027	24/07/2028	23/07/2029	22/07/2030	28/07/2031	26/07/2032	7	4	2
52	Group 4	3rd Tue in September	Tue	3	21/09/2021	20/09/2022	19/09/2023	17/09/2024	16/09/2025	15/09/2026	21/09/2027	19/09/2028	18/09/2029	17/09/2030	16/09/2031	21/09/2032	9	3	3
53	Group 1	1st Thur in September	Thu	4	23/09/2021	22/09/2022	28/09/2023	26/09/2024	25/09/2025	24/09/2026	23/09/2027	28/09/2028	27/09/2029	26/09/2030	25/09/2031	23/09/2032	9	4	5
54	Group 2	4th Thur in September	Thu	4	23/09/2021	22/09/2022	28/09/2023	26/09/2024	25/09/2025	24/09/2026	23/09/2027	28/09/2028	27/09/2029	26/09/2030	25/09/2031	23/09/2032	9	4	5
55	Group 3	1st Thur in October	Thu	1	07/10/2021	06/10/2022	05/10/2023	03/10/2024	02/10/2025	01/10/2026	07/10/2027	05/10/2028	04/10/2029	03/10/2030	02/10/2031	07/10/2032	10	1	5
56	Group 4	4th Wed in October	Wed	4	27/10/2021	26/10/2022	25/10/2023	23/10/2024	22/10/2025	28/10/2026	27/10/2027	25/10/2028	24/10/2029	23/10/2030	22/10/2031	27/10/2032	10	4	4
57	Group 1	1st Wed in December	Wed	1	01/12/2021	07/12/2022	06/12/2023	04/12/2024	03/12/2025	02/12/2026	01/12/2027	06/12/2028	05/12/2029	04/12/2030	03/12/2031	01/12/2032	12	1	4
58	Group 2	2nd Wed in December	Wed	2	08/12/2021	14/12/2022	13/12/2023	11/12/2024	10/12/2025	09/12/2026	08/12/2027	13/12/2028	12/12/2029	11/12/2030	10/12/2031	08/12/2032	12	2	4
Sheet1

 

[Toadstool]

Hi Manexcel,

You can't check a cell colour using a Function but you could just reuse your existing formula and use MATCH.

Manexcel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
6	Group Name	Meeting on	Day	Week	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030	2031	2032	Month No.	Occurance in Month	Day No. in Week
7	Group 1	3rd Tue in January	Tue	3	1/19/2021	1/18/2022	1/17/2023	1/16/2024	1/21/2025	1/20/2026	1/19/2027	1/18/2028	1/16/2029	1/15/2030	1/21/2031	1/20/2032	1	3	3
8	Group 2	1st Thur in February	Thu	1	2/4/2021	2/3/2022	2/2/2023	2/1/2024	2/6/2025	2/5/2026	2/4/2027	2/3/2028	2/1/2029	2/7/2030	2/6/2031	2/5/2032	2	1	5
9	Group 3	3rd Tue in February	Tue	3	2/16/2021	2/15/2022	2/21/2023	2/20/2024	2/18/2025	2/17/2026	2/16/2027	2/15/2028	2/20/2029	2/19/2030	2/18/2031	2/17/2032	2	3	3
10	Group 4	4th Wed in February	Wed	4	2/24/2021	2/23/2022	2/22/2023	2/28/2024	2/26/2025	2/25/2026	2/24/2027	2/23/2028	2/28/2029	2/27/2030	2/26/2031	2/25/2032	2	4	4
11	Group 1	1st Mon in March	Mon	1	3/1/2021	3/7/2022	3/6/2023	3/4/2024	3/3/2025	3/2/2026	3/1/2027	3/6/2028	3/5/2029	3/4/2030	3/3/2031	3/1/2032	3	1	2
12	Group 2	1st Thur in March	Thu	1	3/4/2021	3/3/2022	3/2/2023	3/7/2024	3/6/2025	3/5/2026	3/4/2027	3/2/2028	3/1/2029	3/7/2030	3/6/2031	3/4/2032	3	1	5
13	Group 3	3rd Tue in March	Tue	3	3/16/2021	3/15/2022	3/21/2023	3/19/2024	3/18/2025	3/17/2026	3/16/2027	3/21/2028	3/20/2029	3/19/2030	3/18/2031	3/16/2032	3	3	3
14	Group 4	2nd Thur April	Thu	2	4/8/2021	4/14/2022	4/13/2023	4/11/2024	4/10/2025	4/9/2026	4/8/2027	4/13/2028	4/12/2029	4/11/2030	4/10/2031	4/8/2032	4	2	5
15	Group 1	3rd Thu in April	Thu	3	4/15/2021	4/21/2022	4/20/2023	4/18/2024	4/17/2025	4/16/2026	4/15/2027	4/20/2028	4/19/2029	4/18/2030	4/17/2031	4/15/2032	4	3	5
16	Group 2	4th Wed in April	Wed	4	4/28/2021	4/27/2022	4/26/2023	4/24/2024	4/23/2025	4/22/2026	4/28/2027	4/26/2028	4/25/2029	4/24/2030	4/23/2031	4/28/2032	4	4	4
17	Group 3	3rd Tue in May	Tue	3	5/18/2021	5/17/2022	5/16/2023	5/21/2024	5/20/2025	5/19/2026	5/18/2027	5/16/2028	5/15/2029	5/21/2030	5/20/2031	5/18/2032	5	3	3
18	Group 4	1st Monday in June	Mon	1	6/7/2021	6/6/2022	6/5/2023	6/3/2024	6/2/2025	6/1/2026	6/7/2027	6/5/2028	6/4/2029	6/3/2030	6/2/2031	6/7/2032	6	1	2
19	Group 1	2nd Mon in June	Mon	2	6/14/2021	6/13/2022	6/12/2023	6/10/2024	6/9/2025	6/8/2026	6/14/2027	6/12/2028	6/11/2029	6/10/2030	6/9/2031	6/14/2032	6	2	2
20	Group 2	4th Thur in June	Thu	4	6/24/2021	6/23/2022	6/22/2023	6/27/2024	6/26/2025	6/25/2026	6/24/2027	6/22/2028	6/28/2029	6/27/2030	6/26/2031	6/24/2032	6	4	5
21	Group 3	4th Mon in July	Mon	4	7/26/2021	7/25/2022	7/24/2023	7/22/2024	7/28/2025	7/27/2026	7/26/2027	7/24/2028	7/23/2029	7/22/2030	7/28/2031	7/26/2032	7	4	2
22	Group 4	3rd Tue in September	Tue	3	9/21/2021	9/20/2022	9/19/2023	9/17/2024	9/16/2025	9/15/2026	9/21/2027	9/19/2028	9/18/2029	9/17/2030	9/16/2031	9/21/2032	9	3	3
23	Group 1	1st Thur in September	Thu	4	9/23/2021	9/22/2022	9/28/2023	9/26/2024	9/25/2025	9/24/2026	9/23/2027	9/28/2028	9/27/2029	9/26/2030	9/25/2031	9/23/2032	9	4	5
24	Group 2	4th Thur in September	Thu	4	9/23/2021	9/22/2022	9/28/2023	9/26/2024	9/25/2025	9/24/2026	9/23/2027	9/28/2028	9/27/2029	9/26/2030	9/25/2031	9/23/2032	9	4	5
25	Group 3	1st Thur in October	Thu	1	10/7/2021	10/6/2022	10/5/2023	10/3/2024	10/2/2025	10/1/2026	10/7/2027	10/5/2028	10/4/2029	10/3/2030	10/2/2031	10/7/2032	10	1	5
26	Group 4	4th Wed in October	Wed	4	10/27/2021	10/26/2022	10/25/2023	10/23/2024	10/22/2025	10/28/2026	10/27/2027	10/25/2028	10/24/2029	10/23/2030	10/22/2031	10/27/2032	10	4	4
27	Group 1	1st Wed in December	Wed	1	12/1/2021	12/7/2022	12/6/2023	12/4/2024	12/3/2025	12/2/2026	12/1/2027	12/6/2028	12/5/2029	12/4/2030	12/3/2031	12/1/2032	12	1	4
28	Group 2	2nd Wed in December	Wed	2	12/8/2021	12/14/2022	12/13/2023	12/11/2024	12/10/2025	12/9/2026	12/8/2027	12/13/2028	12/12/2029	12/11/2030	12/10/2031	12/8/2032	12	2	4
29
Sheet1

Cell Formulas
Range	Formula
C7:C28	C7	=TEXT(WEEKDAY(E7), "ddd")
D7:D28	D7	=INT((DAY(E7)-1)/7)+1
E7:P28	E7	=DATE(E$6,$Q7,1+7*$R7)-WEEKDAY(DATE(E$6,$Q7,8-$S7))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A7:D32	Expression	=MATCH(1,($E7:$P7>TODAY())*($E7:$P7<=(TODAY()+30)),0)	text	NO
E7:P28	Expression	=AND(E7>TODAY(),E7<=(TODAY()+30))	text	NO

 

[Peter_SSs]

.. or another way to use the existing condition

Manexcel.xlsm
	A	B	C	D
6	Group Name	Meeting on	Day	Week
7	Group 1	3rd Tue in January	Tue	3
8	Group 2	1st Thur in February	Thu	1
9	Group 3	3rd Tue in February	Tue	3
10	Group 4	4th Wed in February	Wed	4
11	Group 1	1st Mon in March	Mon	1
12	Group 2	1st Thur in March	Thu	1
13	Group 3	3rd Tue in March	Tue	3
14	Group 4	2nd Thur April	Thu	2
15	Group 1	3rd Thu in April	Thu	3
16	Group 2	4th Wed in April	Wed	4
17	Group 3	3rd Tue in May	Tue	3
18	Group 4	1st Monday in June	Mon	1
19	Group 1	2nd Mon in June	Mon	2
20	Group 2	4th Thur in June	Thu	4
21	Group 3	4th Mon in July	Mon	4
22	Group 4	3rd Tue in September	Tue	3
23	Group 1	1st Thur in September	Thu	4
24	Group 2	4th Thur in September	Thu	4
25	Group 3	1st Thur in October	Thu	1
26	Group 4	4th Wed in October	Wed	4
27	Group 1	1st Wed in December	Wed	1
28	Group 2	2nd Wed in December	Wed	2
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A7:D28	Expression	=COUNTIFS($E7:$P7,">"&TODAY(),$E7:$P7,"<="&TODAY()+30)	text	NO

 

[Manexcel]

Hi thank you for your solution using the MATCH function. It worked just great. It is very much appreciated.

 

[Toadstool]

You're welcome!
Thanks for the feedback.