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!

 

[awoohaw]

I guess I should have compared your requirements more carefully. The narrative and the color coding rules have many missing pieces or inconsistencies. Again if you want to count colored cells you need to have a formula that selects the cell to be colored. If it done at the descretion of a manager, to make a calculation there should be additional columns that give an action by a rule. : My questions are in BOLD

• Column A (Recruiters) & S (Coordinators)
  • Blanks or Multiple Names Assigned
  • Is this something that can happen, or not allowed, I see blanks in the sample data so I'm assuming it is permitted.
• Columns B (Job Ref ID) & C (Job Creation Date)
  • If Job Creation Date is greater than 90 days from day report is generated
  • By Report Being Generated, Does this mean that the cell will be highlighted?
• Columns F (Service Line) & G (Practice Area/Campus/SSL)
  • Blanks
  • Does this mean Blanks are permitted?
• Columns I (Job Status) & J (Status)
  • Status mismatch (see below for correct status matches, anything else is mismatch)
    • Created, Interview, Offer, Sourcing = Open
    • On Hold = On Hold, Pipeline
    • Filled = Internal Hire, External Hire
    • Cancelled = Cancelled
    • Does this mapping validation table work?

    • Open	Created
      Open	Interview
      Open	Offer
      Open	Sourcing
      On Hold	Pipeline
      On Hold	On Hold
      Filled	Internal Hire
      Filled	External Hire
      Cancelled	Cancelled

• Column L (Is Job Published)
  • If Job Status = Filled or Cancelled and Column L is true
  • How is Column L Calculated to to be true?
• Columns X, Y, Z, AA, AB, AC
  • If Job Status = Filled or Cancelled and value is greater than 0
  • What value is referenced here? What are the 6 columns?
• Yellow = Data not populated
  • Which Data (column(s)) is being examined here?)
• Red = Job Status / Job Details Status Field Inconsistency
  • Where are the Job Details that the Status is compared to? This should be an exact match.
• Blue = Aged requisition open for 90+ days
  • System date compared to Creation Date?
• Orange = Multiple recruiters assigned
  • How does this relate to Bullet 1 above?
• Purple = Job ad needs to be unposted against req in inactive status
  • I do not see "inactive" as a status above, I'm assuming unposted is an action taken after the report is created. Otherwise, how is "unposted" created?
• Brown = Candidates need to be appropriately dispositioned against req in inactive status
  • Same as above, there is not a status called "inactive", how is this calculated?
• Green = Req in Filled status but no candidate in Hired status
  • There is no column called "Hired" status?

 

[Hol_dav]

Let's concentrate on that last statement... what do you mean by "essentially"?

Are there highlighted cells other than those with errors that you also want to count?

Also, can there be more than one error on a single row?

I meant “essentially” as a summarizing statement. There are no highlighted cells other than those that contain errors according to these parameters:

• Column A (Recruiters) & S (Coordinators)
  • Blanks or Multiple Names Assigned
• Columns B (Job Ref ID) & C (Job Creation Date)
  • If Job Creation Date is greater than 90 days from day report is generated
• Columns F (Service Line) & G (Practice Area/Campus/SSL)
  • Blanks
• Columns I (Job Status) & J (Status)
  • Status mismatch (see below for correct status matches, anything else is mismatch)
    • Created, Interview, Offer, Sourcing = Open
    • On Hold = On Hold, Pipeline
    • Filled = Internal Hire, External Hire
    • Cancelled = Cancelled
• Column L (Is Job Published)
  • If Job Status = Filled or Cancelled and Column L is true
• Columns X, Y, Z, AA, AB, AC
  • If Job Status = Filled or Cancelled and value is greater than 0

I want cells highlighted if they meet the criteria above. Some rows have multiple errors. I want all the errors highlighted, but only the rows counted.

Then I want to count how many rows have at least one cell highlighted. Some rows may have more than 1 cell highlighted (indicating there is more than one issue), but I still want that row to count as one.

I am trying to find out how many “Job Ref ID’s” contain at least one error. Each row corresponds with one “Job Ref ID.”

I hope that makes sense! Any and all help is very much appreciated!!!!

 

[Hol_dav]

I guess I should have compared your requirements more carefully. The narrative and the color coding rules have many missing pieces or inconsistencies. Again if you want to count colored cells you need to have a formula that selects the cell to be colored. If it done at the descretion of a manager, to make a calculation there should be additional columns that give an action by a rule. : My questions are in BOLD

• Column A (Recruiters) & S (Coordinators)
  • Blanks or Multiple Names Assigned
  • Is this something that can happen, or not allowed, I see blanks in the sample data so I'm assuming it is permitted.
• Columns B (Job Ref ID) & C (Job Creation Date)
  • If Job Creation Date is greater than 90 days from day report is generated
  • By Report Being Generated, Does this mean that the cell will be highlighted?
• Columns F (Service Line) & G (Practice Area/Campus/SSL)
  • Blanks
  • Does this mean Blanks are permitted?
• Columns I (Job Status) & J (Status)
  • Status mismatch (see below for correct status matches, anything else is mismatch)
    • Created, Interview, Offer, Sourcing = Open
    • On Hold = On Hold, Pipeline
    • Filled = Internal Hire, External Hire
    • Cancelled = Cancelled
    • Does this mapping validation table work?

    • Open	Created
      Open	Interview
      Open	Offer
      Open	Sourcing
      On Hold	Pipeline
      On Hold	On Hold
      Filled	Internal Hire
      Filled	External Hire
      Cancelled	Cancelled

• Column L (Is Job Published)
  • If Job Status = Filled or Cancelled and Column L is true
  • How is Column L Calculated to to be true?
• Columns X, Y, Z, AA, AB, AC
  • If Job Status = Filled or Cancelled and value is greater than 0
  • What value is referenced here? What are the 6 columns?
• Yellow = Data not populated
  • Which Data (column(s)) is being examined here?)
• Red = Job Status / Job Details Status Field Inconsistency
  • Where are the Job Details that the Status is compared to? This should be an exact match.
• Blue = Aged requisition open for 90+ days
  • System date compared to Creation Date?
• Orange = Multiple recruiters assigned
  • How does this relate to Bullet 1 above?
• Purple = Job ad needs to be unposted against req in inactive status
  • I do not see "inactive" as a status above, I'm assuming unposted is an action taken after the report is created. Otherwise, how is "unposted" created?
• Brown = Candidates need to be appropriately dispositioned against req in inactive status
  • Same as above, there is not a status called "inactive", how is this calculated?
• Green = Req in Filled status but no candidate in Hired status
  • There is no column called "Hired" status?

Background: this report was pulled from an ATS and is measuring our data quality. The parameters listed below are all compliance issues, thus I want those cells highlighted so I can see what needs to be fixed. Then I want to calculate the data quality score to see how compliant the team is.

I have to pull this report every week right now and I am manually highlighting the spreadsheet based on these issues:

• Column A (Recruiters) & S (Coordinators)
  • Blanks or Multiple Names Assigned
  • Is this something that can happen, or not allowed, I see blanks in the sample data so I'm assuming it is permitted.
    • There are blanks in the data. This is because the recruiter or coordinator was not assigned to the requisition in the ATS.
    • I want to highlight these blank cells (they indicate an error/noncompliance)

• Columns B (Job Ref ID) & C (Job Creation Date)
  • If Job Creation Date is greater than 90 days from day report is generated
  • By Report Being Generated, Does this mean that the cell will be highlighted?
    • This means that when I pull the report from the ATS, if the Job Creation Date is greater than 90 days ago, I want to highlight the Job Creation Date Blue
• Columns F (Service Line) & G (Practice Area/Campus/SSL)
  • Blanks
  • Does this mean Blanks are permitted?
    • Blanks are bad because it means the recruiter did not input the required data in the ATS.
    • I want to highlight these cells yellow to indicate missing data (as with columns A and S)
• Columns I (Job Status) & J (Status)
  • Status mismatch (see below for correct status matches, anything else is mismatch)
    • Created, Interview, Offer, Sourcing = Open
    • On Hold = On Hold, Pipeline
    • Filled = Internal Hire, External Hire
    • Cancelled = Cancelled
    • Does this mapping validation table work?

    • Open	Created
      Open	Interview
      Open	Offer
      Open	Sourcing
      On Hold	Pipeline
      On Hold	On Hold
      Filled	Internal Hire
      Filled	External Hire
      Cancelled	Cancelled

    • Awoohaw, I'm not entirely sure what a mapping validation table is or what it does (please forgive me!) but those fields correspond correctly for indicating the data is compliant (right now I highlight both columns I and J red if there are any cells that do not correspond correctly as you outlined in the table)
• Column L (Is Job Published)
  • If Job Status = Filled or Cancelled and Column L is true
  • How is Column L Calculated to to be true?
    • Darn this is part of the parameters that I have for a different table on a different sheet of the workbook. In the future I may start consolidating the two tables, but for now, this info doesn't apply. I am so sorry for the confusion!!!
    • I manually create this column based on whether the "Job Ref ID" starts with "REF"
• Columns X, Y, Z, AA, AB, AC
  • If Job Status = Filled or Cancelled and value is greater than 0
  • What value is referenced here? What are the 6 columns?
    • Same here, this applies to the table on the other sheet :/ sorry!!
• Yellow = Data not populated
  • Which Data (column(s)) is being examined here?)
    • This is for columns a (recruiters), F (service lines), G (Practice Area), S (coordinators) and U (hiring managers)
• Red = Job Status / Job Details Status Field Inconsistency
  • Where are the Job Details that the Status is compared to? This should be an exact match.
    • Columns I (job status) and J (status) - referring to the validation table
• Blue = Aged requisition open for 90+ days
  • System date compared to Creation Date?
    • Oh yes interesting question - can it measure from a static date - when the report/table was created? How should I measure this do you think?
• Orange = Multiple recruiters assigned
  • How does this relate to Bullet 1 above?
    • Oh yes - so I should have included this, but in column A, there are some cells with multiple names (recruiters) listed - I want these cells highlighted if the corresponding "Job Ref ID" does NOT begin with "REF"
• Purple = Job ad needs to be unposted against req in inactive status
  • I do not see "inactive" as a status above, I'm assuming unposted is an action taken after the report is created. Otherwise, how is "unposted" created?
    • Yes yes, ignore, this for the other sheet - my bad!
• Brown = Candidates need to be appropriately dispositioned against req in inactive status
  • Same as above, there is not a status called "inactive", how is this calculated?
    • Doesn't apply, sorry again!
• Green = Req in Filled status but no candidate in Hired status
  • There is no column called "Hired" status?
    • Same here

Awoohaw - I can't tell you how much I appreciate your taking the time to look this over. I love excel, but haven't dabbled much into macros/vba so this is all so new to me!! Let me know what you think I need to do from here

 

[Hol_dav]

I guess I should have compared your requirements more carefully. The narrative and the color coding rules have many missing pieces or inconsistencies. Again if you want to count colored cells you need to have a formula that selects the cell to be colored. If it done at the descretion of a manager, to make a calculation there should be additional columns that give an action by a rule. : My questions are in BOLD

• Column A (Recruiters) & S (Coordinators)
  • Blanks or Multiple Names Assigned
  • Is this something that can happen, or not allowed, I see blanks in the sample data so I'm assuming it is permitted.
• Columns B (Job Ref ID) & C (Job Creation Date)
  • If Job Creation Date is greater than 90 days from day report is generated
  • By Report Being Generated, Does this mean that the cell will be highlighted?
• Columns F (Service Line) & G (Practice Area/Campus/SSL)
  • Blanks
  • Does this mean Blanks are permitted?
• Columns I (Job Status) & J (Status)
  • Status mismatch (see below for correct status matches, anything else is mismatch)
    • Created, Interview, Offer, Sourcing = Open
    • On Hold = On Hold, Pipeline
    • Filled = Internal Hire, External Hire
    • Cancelled = Cancelled
    • Does this mapping validation table work?

    • Open	Created
      Open	Interview
      Open	Offer
      Open	Sourcing
      On Hold	Pipeline
      On Hold	On Hold
      Filled	Internal Hire
      Filled	External Hire
      Cancelled	Cancelled

• Column L (Is Job Published)
  • If Job Status = Filled or Cancelled and Column L is true
  • How is Column L Calculated to to be true?
• Columns X, Y, Z, AA, AB, AC
  • If Job Status = Filled or Cancelled and value is greater than 0
  • What value is referenced here? What are the 6 columns?
• Yellow = Data not populated
  • Which Data (column(s)) is being examined here?)
• Red = Job Status / Job Details Status Field Inconsistency
  • Where are the Job Details that the Status is compared to? This should be an exact match.
• Blue = Aged requisition open for 90+ days
  • System date compared to Creation Date?
• Orange = Multiple recruiters assigned
  • How does this relate to Bullet 1 above?
• Purple = Job ad needs to be unposted against req in inactive status
  • I do not see "inactive" as a status above, I'm assuming unposted is an action taken after the report is created. Otherwise, how is "unposted" created?
• Brown = Candidates need to be appropriately dispositioned against req in inactive status
  • Same as above, there is not a status called "inactive", how is this calculated?
• Green = Req in Filled status but no candidate in Hired status
  • There is no column called "Hired" status?

One last note, I don't want to count the colored cells. I want to count the rows that contain any colored cell. I want to see how many of the Job Ref ID's contain one or many compliance issues.

In a dream world, I could have an automated process for highlighting the cells every time I pull this report. But at the very least, it would be great for an autocalculation of all rows containing any color.

If you think there is a better way to glean this info - I am all ears

 

[awoohaw]

@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.

 

[Rick Rothstein]

See if this macro does what you want...

Sub CountRowsWithColor()
  Dim Cnt As Long, Rng As Range
  For Each Rng In ActiveSheet.UsedRange.Rows
    Cnt = Cnt - (Rng.Interior.Color = 0)
  Next
  MsgBox "There are " & Cnt & " colored rows."
End Sub

 

[awoohaw]

@Hol_dav ,
If @Rick Rothstein's VBA will count the rows with conditional formating you may not need any formulas (other than what is needed for the conditional formating). Here is my take on the conditional formating you need for your initial data import. Please note the questions and comments.

Mr Excel Questions.xlsx
	A	B	C	D
1	Req #	Col Letter	Col Name	Check for this error Condition:
2	Are there any data checks needed for these columns other than being populated as per earlier requirements? Are Columns S & U Populated after your initial data load and review (Manually)?	A, F,G,S,U (A,F,G are audited already in Req 1,3,4)	Recruiters, Service Lines, Practice Area, Coordinators, Hiring Managers
3	Req 1 (The audit check here is first accept anything with one or two names, but reject records with two names with out "REF" in the REF ID. I don't see any records with 2 names in Col A Are names always one name and not a First Last? How are they separated in the column, If there is a second name where is it stored (Same Column or different column If so which one?)	Col A	Recruiters	a. Not One or Two Names b. Two Names IF the JOB REF ID does not have "REF in it.
4	Req 2	Col C	Job Creation Date	Job Creation Date + 90 days >= System Date (Or Date User wants to use)
5	Req 3	Col F	Service Line	Is Blank
6	Req 4	Col G	Practice Area/Campus/SSL	Is Blank
7	Req 5	Col I	Job Status	In Validation List and not be Related to Col J ("Status")
8	Req 6	Col J	Status	In Validation List and not be Related to Col I ("Job Status")
9	Req 7 (See Req 7, this column may not be necessary unless you want to see it for ease purposes.)	Col L	Published	NOT: IF Job Ref ID contains "REF" and Job Status is "Filled" Or Cancelled"
Sheet6

 

[James006]

Hi,

Chip Pearson has explored the topic of Color Functions in Excel

Color Functions In Excel

www.cpearson.com

especially with Function CountColor()

 

[Hol_dav]

See if this macro does what you want...

Sub CountRowsWithColor()
  Dim Cnt As Long, Rng As Range
  For Each Rng In ActiveSheet.UsedRange.Rows
    Cnt = Cnt - (Rng.Interior.Color = 0)
  Next
  MsgBox "There are " & Cnt & " colored rows."
End Sub

Thank you so much, Rick - unfortunately, I'm getting an error: "Compile error: Invalid outside procedure" - how can I fix this?

 

[shinigamilight]

Sub dont()
        Dim rng As Range
        Dim store As Long
        Dim ColoredRows As Long
        Set rng = ActiveSheet.UsedRange       ' change to your range
        Dim k, i As Long
        
        For k = 1 To rng.Rows.Count
                For i = 1 To rng.Columns.Count
                        If rng.Cells(k, i).Interior.Color <> CLng("16777215") Then
                                store = store + 1
                        End If
                Next i
               If store > 0 Then ColoredRows = ColoredRows + 1
                store = 0
        Next k
        
        MsgBox ColoredRows
        
End Sub