Counting color Filled Cells across entire workbook?

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.

Formulas and Functions

Macros and VBA

amit_bhola

Nov 24, 2020

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)

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

CountColoredCells.xlsm32 KB

5 Replies

amit_bhola
Iron Contributor
Nov 23, 2020
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 (?)
- enuff_2live
  Copper Contributor
  Nov 23, 2020
  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.
  - amit_bhola
    Iron Contributor
    Nov 24, 2020
    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)
    
    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
    CountColoredCells.xlsm32 KB

Forum Discussion

Counting color Filled Cells across entire workbook?

5 Replies

Resources