Conditional Formatting Based on Multiple Columns in other Tabs

[covareo]

I have a spreadsheet that I'd like to have conditionally formatted and data consolidated across multiple tabs. I am using Excel for Mac v16.56 on MacOS Monterey.

The first tab is a list of customer visits. the next 5 tabs are lists of customers each tab being a different billing period. The 6 tabs in the spreadsheet are:

• Visits Report
• First of Month
• 3rd of Month
• 5th of Month
• 15th of Month
• End of Month

I'd like to have a macro that I can run each month to do the following formatting operations for each row on the Visits Report tab:

1. Take each name from the Visits Report and search for that name across the other 5 tabs, conditionally formatting the row depending on the tab where it was found (or not found). If the name is found under the following groups, format the row as specified below:
   1. First of Month: Classic Green Fill with Dark Green Text
   2. 3rd of Month: Classic Yellow Fill with Dark Yellow Text
   3. 5th of Month: Classic Light Red Fill with Dark Red Text
   4. 15th of Month: Classic Custom - Pink equivalent Light Pink Fill with Dark Pink Text
   5. End of Month: Classic Custom - Blue equivalent Light Blue Fill with Dark Blue Text
   6. Not Found on any tab: Classic Custom - Gray equivalent Light Gray Fill with Dark Gray Text
2. If that name has payment terms of EFT in column D set the text for that row to Bold and Italic on the visits report tab
3. If there are notes for that name copy them to Visits Report Column D for that name

Here is an example of the raw Visits Report Tab:

Monthly Report - post.xlsx
	A	B	C
1	Date	Client name	Address
2	12/06/2021	Adele Adams	address here
3	12/13/2021	Adele Adams	address here
4	12/27/2021	Adele Adams	address here
5	12/02/2021	Adison Watson	address here
6	12/09/2021	Adison Watson	address here
7	12/16/2021	Adison Watson	address here
8	12/23/2021	Adison Watson	address here
9	12/30/2021	Adison Watson	address here
10	12/03/2021	Alan Grant	address here
11	12/07/2021	Alan Grant	address here
12	12/14/2021	Alan Grant	address here
13	12/21/2021	Alan Grant	address here
14	12/28/2021	Alan Grant	address here
15	12/03/2021	Alexander Baker	address here
16	12/10/2021	Alexander Baker	address here
17	12/17/2021	Alexander Baker	address here
18	12/24/2021	Alexander Baker	address here
19	12/31/2021	Alexander Baker	address here
20	12/02/2021	Emily Barrett	address here
21	12/07/2021	Emily Barrett	address here
22	12/14/2021	Emily Barrett	address here
23	12/21/2021	Emily Barrett	address here
24	12/28/2021	Emily Barrett	address here
25	12/07/2021	Justin Crawford	address here
26	12/14/2021	Justin Crawford	address here
27	12/21/2021	Justin Crawford	address here
28	12/28/2021	Justin Crawford	address here
29	12/01/2021	Michael Watson	address here
30	12/08/2021	Michael Watson	address here
31	12/15/2021	Michael Watson	address here
32	12/22/2021	Michael Watson	address here
33	12/29/2021	Michael Watson	address here
34	12/01/2021	Nicole Martin	address here
35	12/08/2021	Nicole Martin	address here
36	12/15/2021	Nicole Martin	address here
37	12/22/2021	Nicole Martin	address here
38	12/29/2021	Nicole Martin	address here
39	12/01/2021	Stella Davis	address here
40	12/08/2021	Stella Davis	address here
41	12/15/2021	Stella Davis	address here
42	12/22/2021	Stella Davis	address here
43	12/29/2021	Stella Davis	address here
44	12/03/2021	Steven Adams	address here
45	12/07/2021	Steven Adams	address here
46	12/10/2021	Steven Adams	address here
47	12/14/2021	Steven Adams	address here
48	12/17/2021	Steven Adams	address here
49	12/21/2021	Steven Adams	address here
50	12/24/2021	Steven Adams	address here
51	12/28/2021	Steven Adams	address here
52	12/31/2021	Steven Adams	address here
53	12/03/2021	Stuart Andrews	address here
54	12/10/2021	Stuart Andrews	address here
55	12/17/2021	Stuart Andrews	address here
56	12/23/2021	Stuart Andrews	address here
57	12/31/2021	Stuart Andrews	address here
Visits Report

Here is the associated First of Month tab:

Monthly Report - post.xlsx
	A	B	C	D	E
1	Customer	First Name	Last Name	Payment	Notes
2	Barrett, Emily	Emily	Barrett	CC	Declined Last Month
3	Martin, Nicole	Nicole	Martin	EFT	E-MAIL AFTER PAYMENT
First of Month

3rd of Month tab:

Monthly Report - post.xlsx
	A	B	C	D	E
1	Customer	First Name	Last Name	Payment	Notes
2	Andrews, Stuart	Stuart	Andrews	CC
3	Grant, Alan	Alan	Grant	EFT	Notes From Visit
3rd of Month

5th of Month tab:

Monthly Report - post.xlsx
	A	B	C	D	E
1	Customer	First Name	Last Name	Payment	Notes
2	Adams, Adele	Adele	Adams	EFT
3	Crawford, Justin	Justin	Crawford	CC	no serivce last month
5th of Month

15th of Month tab:

Monthly Report - post.xlsx
	A	B	C	D	E
1	Customer	First Name	Last Name	Payment	Notes
2	Adams, Steven	Steven	Adams	CC
3	Baker, Alexander	Alexander	Baker	EFT
15th of Month

End of Month tab:

Monthly Report - post.xlsx
	A	B	C	D	E
1	Customer	First Name	Last Name	Payment	Notes
2	Watson, Adison	Adison	Watson	CC	Email copy of invoice after payment
3	Davis, Stella	Stella	Davis	EFT	PAUSED SERVICE 5/31/22
End of Month

So after all formatting is complete and notes copied to column D the Visits Report would look like this:

Monthly Report - post - formatted.xlsx
	A	B	C	D
1	Date	Client name	Address
2	12/06/2021	Adele Adams	address here
3	12/13/2021	Adele Adams	address here
4	12/27/2021	Adele Adams	address here
5	12/02/2021	Adison Watson	address here	Email copy of invoice after payment
6	12/09/2021	Adison Watson	address here	Email copy of invoice after payment
7	12/16/2021	Adison Watson	address here	Email copy of invoice after payment
8	12/23/2021	Adison Watson	address here	Email copy of invoice after payment
9	12/30/2021	Adison Watson	address here	Email copy of invoice after payment
10	12/03/2021	Alan Grant	address here	Notes From Visit
11	12/07/2021	Alan Grant	address here	Notes From Visit
12	12/14/2021	Alan Grant	address here	Notes From Visit
13	12/21/2021	Alan Grant	address here	Notes From Visit
14	12/28/2021	Alan Grant	address here	Notes From Visit
15	12/03/2021	Alexander Baker	address here
16	12/10/2021	Alexander Baker	address here
17	12/17/2021	Alexander Baker	address here
18	12/24/2021	Alexander Baker	address here
19	12/31/2021	Alexander Baker	address here
20	12/02/2021	Emily Barrett	address here	Declined Last Month
21	12/07/2021	Emily Barrett	address here	Declined Last Month
22	12/14/2021	Emily Barrett	address here	Declined Last Month
23	12/21/2021	Emily Barrett	address here	Declined Last Month
24	12/28/2021	Emily Barrett	address here	Declined Last Month
25	12/07/2021	Justin Crawford	address here	no serivce last month
26	12/14/2021	Justin Crawford	address here	no serivce last month
27	12/21/2021	Justin Crawford	address here	no serivce last month
28	12/28/2021	Justin Crawford	address here	no serivce last month
29	12/01/2021	Michael Watson	address here
30	12/08/2021	Michael Watson	address here
31	12/15/2021	Michael Watson	address here
32	12/22/2021	Michael Watson	address here
33	12/29/2021	Michael Watson	address here
34	12/01/2021	Nicole Martin	address here	E-MAIL AFTER PAYMENT
35	12/08/2021	Nicole Martin	address here	E-MAIL AFTER PAYMENT
36	12/15/2021	Nicole Martin	address here	E-MAIL AFTER PAYMENT
37	12/22/2021	Nicole Martin	address here	E-MAIL AFTER PAYMENT
38	12/29/2021	Nicole Martin	address here	E-MAIL AFTER PAYMENT
39	12/01/2021	Stella Davis	address here	PAUSED SERVICE 5/31/22
40	12/08/2021	Stella Davis	address here	PAUSED SERVICE 5/31/23
41	12/15/2021	Stella Davis	address here	PAUSED SERVICE 5/31/24
42	12/22/2021	Stella Davis	address here	PAUSED SERVICE 5/31/25
43	12/29/2021	Stella Davis	address here	PAUSED SERVICE 5/31/26
44	12/03/2021	Steven Adams	address here
45	12/07/2021	Steven Adams	address here
46	12/10/2021	Steven Adams	address here
47	12/14/2021	Steven Adams	address here
48	12/17/2021	Steven Adams	address here
49	12/21/2021	Steven Adams	address here
50	12/24/2021	Steven Adams	address here
51	12/28/2021	Steven Adams	address here
52	12/31/2021	Steven Adams	address here
53	12/03/2021	Stuart Andrews	address here
54	12/10/2021	Stuart Andrews	address here
55	12/17/2021	Stuart Andrews	address here
56	12/23/2021	Stuart Andrews	address here
57	12/31/2021	Stuart Andrews	address here
Visits Report

Each month I currently physically highlight a printed Visits Report of several thousand rows so this macro would save me so much paper and time! I have figured out how to use conditional formatting rules to change background colors, but not how to combine that with ETF bold/italic AND add notes per row. It seems like a Macro is the right way to go for this but I am open to suggestions as I am WAY out of my depth here! Is this even possible?

 

[covareo]

I do have access to a PC if needed for VBA support