SOLVED

Counting color Filled Cells across entire workbook?

Copper Contributor

I am working on a project to help stream line and improve my works Material Process Sheets. I have made a few Macros and have some conditional formatting in the Workbook. 

 

The Workbook contains Locked and unlocked cells and sheets. Part of the daily process is to check in and track were the material is in the process, weather it is on order or in the machine shop, or even out for treatment. We do this by "highlighting" the cell with the fill feature. I would like to count the last cell in each row when it has been "Highlighted", meaning all processes are done at the material is ready to be assembled. The Workbook can have from one to multiple pages. So here is my question, is there a way to count the filled cells across the Workbook, but limit to a specific set of cells on each Sheet and limit to only look at certain sheets or ignore certain sheets? As I have two sheets that I am using, one for a Data table, for dropdown boxes, and the other for a Reference sheet, that has everything locked so nothing can be changed on it.

 

I have found multiple web results on doing this, but I am wondering if it is possible to do what I am thinking of?

 

Thank you for any help anyone can give on this.

5 Replies

@enuff_2live , if you can decide to assign only specific color shade (cell format) to the cells to be counted across the workbook, then one way is to simply use Ctrl+F (find) dialogue box , expand the options, use cell format as the item to be found , select workbook instead of sheet in "within" and click Find all to know the cell addresses and cell count.

It does not address "ignoring specific cells/specific sheets" requirement, but perhaps it is a ready at hand 70% of the solution (?)

 

amit_bhola_0-1606156566825.png

 

@amit_bholaThank you for the reply.

 

The cells are K16-K45 on the sheets I want to count. The sheets can be copied, if we need more "pages". The cells will be Highlighted using a Macro Button. They are going to be randomly highlighted, as we process the material. So I can't use the Find function. Also I want to record it and show it to keep track of finished parts. I would like to either make it update automatically or with a button.

best response confirmed by enuff_2live (Copper Contributor)
Solution

@enuff_2live , with Macro (VBA) it is possible to count. Attached sheet is an example how it can be done by VBA. It has 2 buttons. 1st button populates the sheet names ("pages") and applicable cells addresses. Then user can type "Y" against the sheets to be considered for calculation. Then the 2nd button calculates the filled cells in the applicable cells of the considered sheets.

 

Though the file is attached, and code is enclosed below as well, still there can be n number of user scenarios which need consideration in VBA programming / errors trapping etc. That level of build and testing is not possible to ensure. May pls. take it as a hint for further development at your end.

(You mentioned that you have created some Macros, so i assume that you can build on, catch the line of the code)

 

amit_bhola_0-1606243710682.png

 

Private Sub PopulateSheetNames_Click()

Dim InputCellAnchor As Range
Dim i As Integer

Set InputCellAnchor = Worksheets("Count Console").Range("B6")

For i = 1 To Worksheets.Count - 1
    InputCellAnchor.Offset(i, 0) = i
    InputCellAnchor.Offset(i, 1) = Worksheets(i + 1).Name
    InputCellAnchor.Offset(i, 2) = "K16:K45"
Next

End Sub
Private Sub CountColoredCells_Click()

Dim InputCellAnchor As Range
Dim OutputCell As Range
Dim sht As Worksheet
Dim rngadd As String
Dim i As Integer        'counter for sheet
Dim j As Integer        'counter for filled cells in the considered sheet
Dim k As Integer        'total filled cells counter

Set InputCellAnchor = Worksheets("Count Console").Range("C7")
Set OutputCell = Worksheets("Count Console").Range("J2")

i = 0
k = 0
OutputCell.Value = ""

Do While InputCellAnchor.Offset(i, 0) <> ""                     'till the case that sheet name is not blank
    Set sht = Worksheets(CStr(InputCellAnchor.Offset(i, 0)))
    rngadd = InputCellAnchor.Offset(i, 1)
    If InputCellAnchor.Offset(i, 2) = "Y" Then                  'if that sheet is inputted as "Y" i.e. considered
        j = 0
        For Each c In sht.Range(rngadd).Cells                   'scan all applicable cells in that sheet
            With c.Interior
                If Not .Pattern = xlNone And _
                       .TintAndShade = 0 And _
                       .PatternTintAndShade = 0 Then
                    
                   j = j + 1                                    'count if it is filled
                   
                End If
            End With
        Next
        InputCellAnchor.Offset(i, 3) = j                        'output sheet level count
        k = k + j
    Else
        InputCellAnchor.Offset(i, 3) = ""
    End If
    i = i + 1
Loop

OutputCell.Value = k                                            'output global count

End Sub

@amit_bhola Wow, that is really close to what I am looking for. I just tried it in my Workbook and was able to get it to mostly work. Will just need to see if I can get it to work under one button. Thank you for the help. This was extremely helpful.

Glad to know that it helped.
Yes, as per your actual workbook needs, it may need some adjustments at your end, e.g. in template I shared, the console sheet is assumed to be always the 1st sheet so it is not populated as a sheet name by 1st button. The 1st button is only for input convenience, not required again and again once the pages have been listed.
Console type of input method adds to flexibility for future, e.g. if the cells addresses expand to a bigger range than just K16:K45 in future, or if the cells addresses are different for different sheets, it can be easily incorporated just by typing in console sheet.
1 best response

Accepted Solutions
best response confirmed by enuff_2live (Copper Contributor)
Solution

@enuff_2live , with Macro (VBA) it is possible to count. Attached sheet is an example how it can be done by VBA. It has 2 buttons. 1st button populates the sheet names ("pages") and applicable cells addresses. Then user can type "Y" against the sheets to be considered for calculation. Then the 2nd button calculates the filled cells in the applicable cells of the considered sheets.

 

Though the file is attached, and code is enclosed below as well, still there can be n number of user scenarios which need consideration in VBA programming / errors trapping etc. That level of build and testing is not possible to ensure. May pls. take it as a hint for further development at your end.

(You mentioned that you have created some Macros, so i assume that you can build on, catch the line of the code)

 

amit_bhola_0-1606243710682.png

 

Private Sub PopulateSheetNames_Click()

Dim InputCellAnchor As Range
Dim i As Integer

Set InputCellAnchor = Worksheets("Count Console").Range("B6")

For i = 1 To Worksheets.Count - 1
    InputCellAnchor.Offset(i, 0) = i
    InputCellAnchor.Offset(i, 1) = Worksheets(i + 1).Name
    InputCellAnchor.Offset(i, 2) = "K16:K45"
Next

End Sub
Private Sub CountColoredCells_Click()

Dim InputCellAnchor As Range
Dim OutputCell As Range
Dim sht As Worksheet
Dim rngadd As String
Dim i As Integer        'counter for sheet
Dim j As Integer        'counter for filled cells in the considered sheet
Dim k As Integer        'total filled cells counter

Set InputCellAnchor = Worksheets("Count Console").Range("C7")
Set OutputCell = Worksheets("Count Console").Range("J2")

i = 0
k = 0
OutputCell.Value = ""

Do While InputCellAnchor.Offset(i, 0) <> ""                     'till the case that sheet name is not blank
    Set sht = Worksheets(CStr(InputCellAnchor.Offset(i, 0)))
    rngadd = InputCellAnchor.Offset(i, 1)
    If InputCellAnchor.Offset(i, 2) = "Y" Then                  'if that sheet is inputted as "Y" i.e. considered
        j = 0
        For Each c In sht.Range(rngadd).Cells                   'scan all applicable cells in that sheet
            With c.Interior
                If Not .Pattern = xlNone And _
                       .TintAndShade = 0 And _
                       .PatternTintAndShade = 0 Then
                    
                   j = j + 1                                    'count if it is filled
                   
                End If
            End With
        Next
        InputCellAnchor.Offset(i, 3) = j                        'output sheet level count
        k = k + j
    Else
        InputCellAnchor.Offset(i, 3) = ""
    End If
    i = i + 1
Loop

OutputCell.Value = k                                            'output global count

End Sub

View solution in original post