How to calculate number of rows containing any colored cells?

[Hol_dav]

I am wondering if there is a VBA/macro that I can run to count the number of rows in my table that contain any colored cells?

My research seems to only turn up code for counting colored cells, but I need to count the entire row as 1, regardless of how many or what color cells are contained.

Please help me!

 

[Hol_dav]

I cannot see how that error is being generated from the code I posted. I tested my code on XL2019 (I don't have access to a version of XL365 to test VBA code on) before I posted it and it worked fine for me... I just retested the code and it still works fine for me. Did you copy my code exactly as I posted it? I almost sounds like you omitted the Sub statement from my code as that would produce the error you are describing whenever you attempted to run a different macro.

Let me try again!

 

[Hol_dav]

Let me try again!

Rick, it worked!! That's brilliant!! So now if I was to make a new workbook, could I still run this macro for that new one?

 

[Hol_dav]

@Hol_dav , None of what I'm going to do involes VBA or macros. Although, I can attempt VBA it would take me forever and there are other experts here better than. me. What you're asking for can be done with conditional formatting. Getting a report of all the errors can also be done, but will take some time to get the formulas right. My formula skills are decent, but rusty.

I am going to take your updated comments regarding the columns and calcuation and summarize them again That will take more than a minute!

My only other question is how you manage this workbook. Will you bring new data into the workbook each month? Is it consistently formatted (column positions, etc..)? If you're having a new workbook each month, then you'll probably need to create a blank template with the formulas and conditional formatting.. and be sure to import it the same way each time. Power Pivot and Power Query may help in some instances (I am not an expert on those and cannot begin to tell you how to start that.) But those are long term questions.

Stay tuned for another message.

Hi Awoohaw, just following up on this. Totally get that the conditional formulas are a big lift, but at the very least, can you explain to me about templates? If I create a blank template with these formulas, you’re saying there is a way to import my report into the template?

 

[awoohaw]

I'll be working on your conditional formatting in a few hours. To create a template would be copying the workbook with the conditional formating you want to keep, deleting the data that changes, and renaming it *.xlst. And you would save it in a folder where you know where it is.

 

[Hol_dav]

I'll be working on your conditional formatting in a few hours. To create a template would be copying the workbook with the conditional formating you want to keep, deleting the data that changes, and renaming it *.xlst. And you would save it in a folder where you know where it is.

Ok great. Then on a weekly basis, when I download the report from the ATS, do I just save the data into a new worksheet. Then manually copy the data onto the template and save again so that my conditional formatting applied?

 

[awoohaw]

yes, I'm working on it now. its really complicated!

 

[awoohaw]

Okay, because your workbook was so wide, I created a new workbook with only the columns that need the conditional formatting. Hopefully you can replicate in your workbook(s). Or build your workbook into this. But, I think the conditional formatting won't transfer with your copy so you need to put that in manually.
The conditional formating is only in the first 21 or so rows. I would suggest only putting in the first data row of your template, and then use format painter to copy the formatting down after youve done you paste.

In the following your data elements are to the left of the big dark columns. To the right are the true/false formulas I used to create the conditional format rules.

Additionally, I did not do any validation on the user names for the 2 person data entry lists for recruiters. There is probably a way to do that, but its beyond my formula skills.

the second "Status" column takes advantage of a filter based based on the Job Status Column.

The second worksheet has ranges used in the validations. If you need to add values to these ranges you need to update the names from the Name Manager in the Formulas Menu. The first column is truncated.

To Create a template,
Open a New workbook.
Copy the second worksheet ranges and name the the named ranges one worksheet.
Put your header information on the main sheet.
Add the conditional formatting rules to the first data row.

Save as .xlst file in a location you like.

You may want to make it read only.

Conditional Formating.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Recruiters	Job Ref ID	Job Creation Date	Service Line	Practice Area/Campus/SSL	Job Status	Status	Coordinators	Hiring Managers				Recruiters Check	Creation Date Check	Job Status Check	Status Check	Service Line Check	Practice Area/Campus/SSLCheck
2	Megan	R1010173	9/15/2022			CREATED	Open		Deep				FALSE	TRUE	FALSE	FALSE	TRUE	TRUE
3	Chavon, Diane	R1013816	9/20/2023			CREATED	Open		Stephen				TRUE	TRUE	FALSE	FALSE	TRUE	TRUE
4		R1010662	8/18/2022			CREATED	Open		Destiny				TRUE	TRUE	FALSE	FALSE	TRUE	TRUE
5	George	R1011173	8/24/2022			CREATED	Open		Destiny				TRUE	TRUE	FALSE	FALSE	TRUE	TRUE
6	Megan	R1014085	12/2/2022	Media	Media Effectiveness	SOURCING	Internal Hire		Yuling				FALSE	FALSE	FALSE	TRUE	FALSE	FALSE
7	Chavon, Diane	R0995484	4/21/2022	Solutions		ON HOLD	Open		Samuel				TRUE	TRUE	TRUE	TRUE	FALSE	TRUE
8	Megan	R0944018	3/26/2021	Solutions	Solutions Ecosystem & Growth Operations	OFFER	Open		Masako				FALSE	TRUE	FALSE	FALSE	FALSE	FALSE
9	Chavon, Diane	R0943410	3/24/2021	Microsoft	Media Effectiveness	ON_HOLD	On Hold		Gary				TRUE	TRUE	FALSE	FALSE	FALSE	FALSE
10	Chavon, Diane	REF20444N	4/8/2022	Creative	Integrated Production	CREATED	Open		Brooke				FALSE	TRUE	FALSE	FALSE	FALSE	FALSE
11		R1000205	5/24/2022	Media	Media Effectiveness	ON_HOLD	On Hold		Michael				TRUE	TRUE	FALSE	FALSE	FALSE	FALSE
12		R1008992	8/1/2022	Media	Performance Marketing	ON_HOLD	On Hold		Eric				TRUE	TRUE	FALSE	FALSE	FALSE	FALSE
13		R0960324	8/11/2021	Microsoft	Media Effectiveness	ON_HOLD	On Hold		Xiaoyuan				TRUE	TRUE	FALSE	FALSE	FALSE	FALSE
14		R0949060	5/11/2021	Creative	Client	OFFER	Open		Ashley				TRUE	TRUE	FALSE	FALSE	FALSE	FALSE
15		R0992670	3/25/2022	Creative	Integrated Production	OFFER	Open		Concetta							FALSE	FALSE	FALSE
16		R0964913	9/28/2021	Microsoft	Media Effectiveness	ON_HOLD	On Hold		Christina							FALSE	FALSE	FALSE
Complx Cond Frmtng wksht

Cell Formulas
Range	Formula
M2:M14	M2	=IF( COUNTA(TEXTSPLIT(A2,","))=2, IF(ISNUMBER(FIND("REF",B2,1))=FALSE,TRUE,FALSE), ISNUMBER(MATCH(A2,RecruiterNames,0))=FALSE)
N2:N14	N2	= OR( C2+90<TODAY(), C2>TODAY())
O2:O14	O2	=ISNUMBER(MATCH(F2,JobStatusNames,0))=FALSE
P2:P16	P2	=ISNUMBER(MATCH(G2,FILTER(StatusNamesToFilter,JobStatusFilter=F2),0))=FALSE
Q2:R16	Q2	=D2= ""

Named Ranges
Name	Refers To	Cells
JobStatusFilter	='Compare Lists'!$G$2:$G$10	P2:P16
JobStatusNames	='Compare Lists'!$C$2:$C$8	O2:O14
RecruiterNames	='Compare Lists'!$A$2:$A$60	M2:M14
StatusNamesToFilter	='Compare Lists'!$H$2:$H$10	P2:P16

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:G18	Expression	=ISNUMBER(MATCH(G2,FILTER(StatusNamesToFilter,JobStatusFilter=F2),0))=FALSE	text	NO
F2:F21	Expression	=ISNUMBER(MATCH(F2,JobStatusNames,0))=FALSE	text	NO
E2:E21	Expression	=E2= ""	text	NO
D2:D21	Expression	=D2= ""	text	NO
C2:C21	Expression	= OR( C2+90<TODAY(), C2>TODAY())	text	NO
A2:A21	Expression	=IF( COUNTA(TEXTSPLIT(A2,","))=2, IF(ISNUMBER(FIND("REF",B2,1))=FALSE,TRUE,FALSE), ISNUMBER(MATCH(A2,RecruiterNames,0))=FALSE)	text	NO

Conditional Formating.xlsx
	A	B	C	D	E	F	G	H	I
1	RecruiterNames		JobStatusNames		StatusNames		JobStatusFilter	StatusNamesToFilter
2	Megan Saunders		CANCELLED		External Hire		CANCELLED	Cancelled
3	Sebastien Reynolds		CREATED		Internal Hire		CREATED	Open
4	John Smith		FILLED		On Hold		FILLED	Internal Hire
5	Adam		INTERVIEW		Open		FILLED	External Hire
6	Adeel		OFFER		Pipeline		INTERVIEW	Open
7	Aleta		ON_HOLD				OFFER	Open
8	Alexander		SOURCING				ON_HOLD	On Hold
9	Amanda						ON_HOLD	Pipeline
10	Amy						SOURCING	Open
11	Anabel
12	Anthony
13	Arielle
14	Ashley
15	Brianna
16	Chavon
17	Chris
18	Corinna
19	Cyndi
20	Desiree
21	Destiny
Compare Lists

Cells with Data Validation
Cell	Allow	Criteria
H13	List	=$C$2:$C$8

 

[Hol_dav]

Okay, because your workbook was so wide, I created a new workbook with only the columns that need the conditional formatting. Hopefully you can replicate in your workbook(s). Or build your workbook into this. But, I think the conditional formatting won't transfer with your copy so you need to put that in manually.
The conditional formating is only in the first 21 or so rows. I would suggest only putting in the first data row of your template, and then use format painter to copy the formatting down after youve done you paste.

In the following your data elements are to the left of the big dark columns. To the right are the true/false formulas I used to create the conditional format rules.

Additionally, I did not do any validation on the user names for the 2 person data entry lists for recruiters. There is probably a way to do that, but its beyond my formula skills.

the second "Status" column takes advantage of a filter based based on the Job Status Column.

The second worksheet has ranges used in the validations. If you need to add values to these ranges you need to update the names from the Name Manager in the Formulas Menu. The first column is truncated.

To Create a template,
Open a New workbook.
Copy the second worksheet ranges and name the the named ranges one worksheet.
Put your header information on the main sheet.
Add the conditional formatting rules to the first data row.

Save as .xlst file in a location you like.

You may want to make it read only.

Conditional Formating.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Recruiters	Job Ref ID	Job Creation Date	Service Line	Practice Area/Campus/SSL	Job Status	Status	Coordinators	Hiring Managers				Recruiters Check	Creation Date Check	Job Status Check	Status Check	Service Line Check	Practice Area/Campus/SSLCheck
2	Megan	R1010173	9/15/2022			CREATED	Open		Deep				FALSE	TRUE	FALSE	FALSE	TRUE	TRUE
3	Chavon, Diane	R1013816	9/20/2023			CREATED	Open		Stephen				TRUE	TRUE	FALSE	FALSE	TRUE	TRUE
4		R1010662	8/18/2022			CREATED	Open		Destiny				TRUE	TRUE	FALSE	FALSE	TRUE	TRUE
5	George	R1011173	8/24/2022			CREATED	Open		Destiny				TRUE	TRUE	FALSE	FALSE	TRUE	TRUE
6	Megan	R1014085	12/2/2022	Media	Media Effectiveness	SOURCING	Internal Hire		Yuling				FALSE	FALSE	FALSE	TRUE	FALSE	FALSE
7	Chavon, Diane	R0995484	4/21/2022	Solutions		ON HOLD	Open		Samuel				TRUE	TRUE	TRUE	TRUE	FALSE	TRUE
8	Megan	R0944018	3/26/2021	Solutions	Solutions Ecosystem & Growth Operations	OFFER	Open		Masako				FALSE	TRUE	FALSE	FALSE	FALSE	FALSE
9	Chavon, Diane	R0943410	3/24/2021	Microsoft	Media Effectiveness	ON_HOLD	On Hold		Gary				TRUE	TRUE	FALSE	FALSE	FALSE	FALSE
10	Chavon, Diane	REF20444N	4/8/2022	Creative	Integrated Production	CREATED	Open		Brooke				FALSE	TRUE	FALSE	FALSE	FALSE	FALSE
11		R1000205	5/24/2022	Media	Media Effectiveness	ON_HOLD	On Hold		Michael				TRUE	TRUE	FALSE	FALSE	FALSE	FALSE
12		R1008992	8/1/2022	Media	Performance Marketing	ON_HOLD	On Hold		Eric				TRUE	TRUE	FALSE	FALSE	FALSE	FALSE
13		R0960324	8/11/2021	Microsoft	Media Effectiveness	ON_HOLD	On Hold		Xiaoyuan				TRUE	TRUE	FALSE	FALSE	FALSE	FALSE
14		R0949060	5/11/2021	Creative	Client	OFFER	Open		Ashley				TRUE	TRUE	FALSE	FALSE	FALSE	FALSE
15		R0992670	3/25/2022	Creative	Integrated Production	OFFER	Open		Concetta							FALSE	FALSE	FALSE
16		R0964913	9/28/2021	Microsoft	Media Effectiveness	ON_HOLD	On Hold		Christina							FALSE	FALSE	FALSE
Complx Cond Frmtng wksht

Cell Formulas
Range	Formula
M2:M14	M2	=IF( COUNTA(TEXTSPLIT(A2,","))=2, IF(ISNUMBER(FIND("REF",B2,1))=FALSE,TRUE,FALSE), ISNUMBER(MATCH(A2,RecruiterNames,0))=FALSE)
N2:N14	N2	= OR( C2+90<TODAY(), C2>TODAY())
O2:O14	O2	=ISNUMBER(MATCH(F2,JobStatusNames,0))=FALSE
P2:P16	P2	=ISNUMBER(MATCH(G2,FILTER(StatusNamesToFilter,JobStatusFilter=F2),0))=FALSE
Q2:R16	Q2	=D2= ""

Named Ranges
Name	Refers To	Cells
JobStatusFilter	='Compare Lists'!$G$2:$G$10	P2:P16
JobStatusNames	='Compare Lists'!$C$2:$C$8	O2:O14
RecruiterNames	='Compare Lists'!$A$2:$A$60	M2:M14
StatusNamesToFilter	='Compare Lists'!$H$2:$H$10	P2:P16

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:G18	Expression	=ISNUMBER(MATCH(G2,FILTER(StatusNamesToFilter,JobStatusFilter=F2),0))=FALSE	text	NO
F2:F21	Expression	=ISNUMBER(MATCH(F2,JobStatusNames,0))=FALSE	text	NO
E2:E21	Expression	=E2= ""	text	NO
D2:D21	Expression	=D2= ""	text	NO
C2:C21	Expression	= OR( C2+90<TODAY(), C2>TODAY())	text	NO
A2:A21	Expression	=IF( COUNTA(TEXTSPLIT(A2,","))=2, IF(ISNUMBER(FIND("REF",B2,1))=FALSE,TRUE,FALSE), ISNUMBER(MATCH(A2,RecruiterNames,0))=FALSE)	text	NO

Conditional Formating.xlsx
	A	B	C	D	E	F	G	H	I
1	RecruiterNames		JobStatusNames		StatusNames		JobStatusFilter	StatusNamesToFilter
2	Megan Saunders		CANCELLED		External Hire		CANCELLED	Cancelled
3	Sebastien Reynolds		CREATED		Internal Hire		CREATED	Open
4	John Smith		FILLED		On Hold		FILLED	Internal Hire
5	Adam		INTERVIEW		Open		FILLED	External Hire
6	Adeel		OFFER		Pipeline		INTERVIEW	Open
7	Aleta		ON_HOLD				OFFER	Open
8	Alexander		SOURCING				ON_HOLD	On Hold
9	Amanda						ON_HOLD	Pipeline
10	Amy						SOURCING	Open
11	Anabel
12	Anthony
13	Arielle
14	Ashley
15	Brianna
16	Chavon
17	Chris
18	Corinna
19	Cyndi
20	Desiree
21	Destiny
Compare Lists

Cells with Data Validation
Cell	Allow	Criteria
H13	List	=$C$2:$C$8

Thank you so much!! This is so extensive, I really appreciate the time you took to help me!!

 

[awoohaw]

well, that's only half way through. Then you need to insert the vba that others have posted to count your colored cells.
And speaking of that. You may want to test all the color counting on a just a few rows. You need to make sure the color index number (If that is what it is called) can be recognized. If not you need to experiment and find out which colors to use.

But, now that you have these rules you can use them to create a count of errors per row and sum them.

 

[awoohaw]

And, you're welcome. I enjoy excel challenges! I'm happy to have some of them result in practicable workbooks.