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]

@Hol_dav , do you still need the conditional formatting applied? If so, those rules need to be defined.

 

[Fluff]

do you still need the conditional formatting applied?

What conditional formatting? In post#4 the OP said

They are colored manually.

 

[Hol_dav]

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.

• Yes, so that's why I thought I needed a VBA code, because I will need the same formulas run every week - I am not familiar with Power Pivot or Power Query :/ How do I import my report into a blank template with the formulas already set up? I suppose I can google this...
• The columns will be the same every week and I will need to conditionally format the same info (or lack thereof)

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)?

• Only other check is if there are multiple names in column A AND column B does not start with REF, THEN highlight those cells in column A orange.
• No we do not edit or add data to the report. It is just to give us a visual of compliance issues

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?)

• Oh ok, I took out last names in the swappit! The names are formatted: Megan Saunders
• And for multiple names there is a comma in-between the people's names but no spaces: Megan Saunders,Sebastien Reynolds
• I just took a sample of the table I have and didn't realize there weren't any multiple names in it - ugh sorry about that, I hate to be a pain!

Req 5

Col I

Job Status

In Validation List and not be Related to Col J ("Status")

Req 6

Col J

Status

In Validation List and not be Related to Col I ("Job Status")

• You lost me on the "not related to" - Only one of these columns needs to be audited. But preferably adjoining cells (col I & J) are highlighted.

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"

• I guess, not necessary for this spreadsheet!

 

[Hol_dav]

What conditional formatting? In post#4 the OP said

Right now they are manually applied because I though the only way to format them would be using VBA and I don't know how to do that lol I run this report every week, so preferably there is an automated way to format! Just not sure how I go about importing a report into a spreadsheet template with formulas already. Didn't even know that was possible!

 

[Hol_dav]

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

Hey! Thanks - what/where exactly do I change my range? Please forgive me!!

 

[Hol_dav]

Hi,

Chip Pearson has explored the topic of Color Functions in Excel

Color Functions In Excel

www.cpearson.com

especially with Function CountColor()

CountColor(), from what I understand, only gives me the number of cells colored. I need to count the rows that contain any color.

 

[awoohaw]

Right now they are manually applied because I though the only way to format them would be using VBA and I don't know how to do that lol I run this report every week, so preferably there is an automated way to format! Just not sure how I go about importing a report into a spreadsheet template with formulas already. Didn't even know that was possible!

ok. i'll see what i can do to get it those conditional formatting rules to work

 

[shinigamilight]

like this

range("A1:D11")

 

[Hol_dav]

like this

range("A1:D11")

So like this:

Sub dont()
        Dim rng As Range
        Dim store As Long
        Dim ColoredRows As Long
        Set rng = ActiveSheet.UsedRange       ' range("A2:U586")
        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

 

[Rick Rothstein]

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

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.