Nov 23 2020 08:26 AM
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.
Nov 23 2020 10:36 AM - edited Nov 23 2020 10:39 AM
@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 (?)
Nov 23 2020 01:02 PM
@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.
Nov 24 2020 10:50 AM
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)
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
Nov 24 2020 05:01 PM - edited Nov 24 2020 05:02 PM
@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.
Nov 24 2020 07:11 PM
Nov 24 2020 10:50 AM
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)
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