Search for & Count Blank Cells of a certain color based on Y/N dropdown menus

[TheTiredEngineer]

OK so I have this bit of code that Im trying to figure out. Im hoping for a quick resolution.

Option Explicit

Dim MyRange, MR, rngCell As Range
Dim rngCol, c, lockRange, lockCell As Range
Dim rngCount, TotalBlanks, LockCount As Long
Dim rngCellValue As String

Sub CheckEmptyCellsButton_Click()
  
    Set MR = Range("A1:AF184")
    Set rngCol = Range("C1:C145")
    
    rngCount = 1
    TotalBlanks = 1
    
    'Need a second For Each loop to select each of the Y/N boxes and compare if Y, then check if cells are blank
 
 For Each c In rngCol
 If c.Value = "Y" Then
 Debug.Print "Y cells selected"
 End If
Next c

    For Each rngCell In MR
        If rngCell.DisplayFormat.Interior.Color = RGB(255, 245, 217) Then
            'Sheets("Results").Range("A" & rngCount) = rngCell.Value
            rngCount = rngCount + 1 'counts the number of fillable yellow cells

            If Len(rngCell.Formula) = 0 Then 'checks if a cell is blank
            
            TotalBlanks = TotalBlanks + 1
                
                Debug.Print rngCell.Address & " is blank"
                Debug.Print TotalBlanks
            End If
        End If
    Next rngCell

End Sub

if I want to pair this code with an if loop to check each drop down is Y or N and then only count blanks that are in the same section as that Y/N? I imagine it will be a for loop to count the drop-down rows and an if statement to check if Y. Can I pair that with the Len(rngCell.Formula)=0 ? Currently its checking all the blanks and obviously if the dropdown in N, then I dont want it checking those cells. Ive tried a few different For loops today and I kept getting stuck in infinite loops and it wasnt working.

 

[myall_blues]

When you say you want to "count blanks that are in the same section as that Y/N", what section. Do you mean the same row as the value in C (i.e if C1 then check for blanks in A1:AA1)? In your code the rows covered by rngCol are different to those covered by MR.

Also as a sidenote, your Dim statements are not doing what you possibly think they are doing. For example in the line Dim rngCount, TotalBlanks, LockCount As Long only LockCount is declared as Long. The others are Variant in the absence of a declaration. If you want them all to be Long then you must declare every variable like so:

Dim rngCount as Long, TotalBlanks as Long, LockCount As Long

 

[TheTiredEngineer]

So the code needs to check Component W, X and Y, and if Col B is selected Y, then loop to search the empty cells of that yellow color for only those items.

Currently the loop shows me all of the empty spots which isnt what I want.

 

[TheTiredEngineer]

I dont know how to edit my post, so Im going to add this here:

Basically, heres how I envision the code to function and correct me if Im wrong. Check Col B line by line for Y or N. If Y, then use the For loop to find the empty yellow cells until the next Y or N in Col B.

 

[TheTiredEngineer]

Ive tried doing a few variations. This is the closest Ive gotten so far, but the code isnt counting right.

Sub CountColoredEmptyCells()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim j As Long
    Dim count As Long
    Dim columnToSearch As String, resultColumn As String
    Dim colorColumns As Variant
    Dim startCountRow As Long, totalEmptyCount As Long
   
    ' Set your worksheet, columns and other parameters
    Set ws = ThisWorkbook.Sheets("Sheet1")
    columnToSearch = "B" ' Column where you are searching for Y and N
    colorColumns = Array("G", "I") ' Columns to check for empty cells with specific color
    lastRow = ws.Cells(ws.Rows.count, columnToSearch).End(xlUp).Row

'    On Error GoTo ErrorHandler
   
    For i = 1 To lastRow
        If ws.Cells(i, columnToSearch).Value = "Y" Then
            count = 0
            startCountRow = i + 1 ' Start counting from the current row
            Do While ws.Cells(startCountRow, columnToSearch).Value <> "N" And startCountRow <= lastRow
                For j = LBound(colorColumns) To UBound(colorColumns)
                    If ws.Cells(startCountRow, ws.Columns(colorColumns(j)).Column).Value = "" Then
                        If ws.Cells(startCountRow, ws.Columns(colorColumns(j)).Column).Interior.Color = RGB(255, 245, 217) Then
                            count = count + 1
                        End If
                    End If
                Next j
                startCountRow = startCountRow + 1
                         
            Loop
            totalEmptyCount = totalEmptyCount + count
            ' Output the result in the resultColumn next to the Y cell
            Debug.Print "Y cell found at row " & i & ", column " & " | Total empty cells per row: " & count '& colorColumns(j)
            Debug.Print "Total Empty Count over all the sheet is " & totalEmptyCount
        End If
    Next i
   
'ErrorHandler:
'    MsgBox "An error occured " & Err.Description
End Sub

I should be getting total count = 12, but its stuck at 8.

 

[TheTiredEngineer]

When you say you want to "count blanks that are in the same section as that Y/N", what section. Do you mean the same row as the value in C (i.e if C1 then check for blanks in A1:AA1)? In your code the rows covered by rngCol are different to those covered by MR.

Didnt tag you. Sorry I need a fast answer to this. Ive been pulling my hair for the last 2 days stuck on this bit of code.

 

[myall_blues]

@TheTiredEngineer try this.

Sub Tired()
    Dim Brange As Range, currentCell As Range
    Dim lastRow As Long, count As Long, i As Long, j As Long
    lastRow = Range("B" & Rows.count).End(xlUp).Row
    Set Brange = Range("B6:B" & lastRow)
    count = 0
    For Each currentCell In Brange
        If currentCell.Value = "Y" Then
            For i = 0 To 1
                For j = 7 To 9 Step 2
                    Select Case True
                        Case Cells(currentCell.Row + i, j).Value = "" And _
                            Cells(currentCell.Row + i, j).Interior.Color = RGB(255, 245, 217)
                            count = count + 1
                            Debug.Print Cells(currentCell.Row + i, j).Address & "; " & count
                        Case Else
                            ' do nothing
                    End Select
                Next j
            Next i
        End If
    Next
    MsgBox ("Total cells was " & count)
End Sub

 

[TheTiredEngineer]

@TheTiredEngineer try this.

Sub Tired()
    Dim Brange As Range, currentCell As Range
    Dim lastRow As Long, count As Long, i As Long, j As Long
    lastRow = Range("B" & Rows.count).End(xlUp).Row
    Set Brange = Range("B6:B" & lastRow)
    count = 0
    For Each currentCell In Brange
        If currentCell.Value = "Y" Then
            For i = 0 To 1
                For j = 7 To 9 Step 2
                    Select Case True
                        Case Cells(currentCell.Row + i, j).Value = "" And _
                            Cells(currentCell.Row + i, j).Interior.Color = RGB(255, 245, 217)
                            count = count + 1
                            Debug.Print Cells(currentCell.Row + i, j).Address & "; " & count
                        Case Else
                            ' do nothing
                    End Select
                Next j
            Next i
        End If
    Next
    MsgBox ("Total cells was " & count)
End Sub

This worked amazingly. Thank you so much!

 

[myall_blues]

You’re welcome. Thanks for the feedback.

 

[TheTiredEngineer]

You’re welcome. Thanks for the feedback.

I wanted to ask you whats the

For j = 7 To 9 Step 2

mean? what if I have more than 2 columns of empty cells and more than 2 rows below the dropdown row? Do I have to change any of the count numbers or will it do it all automatically?