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.

 

[TheTiredEngineer]

Like for example, I tried changing the iteration for i to 0 to 2 but it seemed like it was double counting. I think Id effectively have to have an equal number of rows between each of the dropdown sections. Am I understanding that correctly? If so, then how would it be if the rows arent all equal like the below screenshot?

 

[myall_blues]

It seems our respective world time zones are not conducive to quick answers .
The 7 and 9 assigned to j represent columns G and I respectively, so if you need to go further across the page you need to change those numbers. It wouldn’t matter if you didn’t step by 2 as it will ignore any non-yellow cells, so it could be changed to 6 to 26 for example.
Regarding the rows, it double counted row 8 when you modified it, because the Y at row 6 then counted down 3 rows and included 8, and the Y at 8 counted that row again. Either:

• the rows need to be equally spaced
• there needs to be some identifier to ’group’ cells
• I could modify it to stop counting once it encounters a non-yellow.

 

[myall_blues]

Try this modified code. This will work for unequal rows as long as there is at least one blank row between each successive group (per your example).
Option Explicit

Sub Tired()
    Const testCol As Long = 7
    Dim Brange As Range, currentCell As Range
    Dim lastRow As Long, count As Long, i As Long, j As Long
    Dim msgText As String
    msgText = "Empty cells: "
    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
            i = 0
            Do While Cells(currentCell.Row + i, testCol).Interior.Color = RGB(255, 245, 217)
                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
                            If Len(msgText) Mod 50 = 0 Then
                                msgText = msgText & vbCrLf & Cells(currentCell.Row + i, j).Address & " "
                            Else
                                msgText = msgText & Cells(currentCell.Row + i, j).Address & " "
                            End If
                        Case Else
                            ' do nothing
                    End Select
                Next j
                i = i + 1
            Loop
        End If
    Next
    msgText = msgText & vbCrLf & "Total cells was " & count
    MsgBox msgText
End Sub

 

[TheTiredEngineer]

Try this modified code. This will work for unequal rows as long as there is at least one blank row between each successive group (per your example).
Option Explicit

Sub Tired()
    Const testCol As Long = 7
    Dim Brange As Range, currentCell As Range
    Dim lastRow As Long, count As Long, i As Long, j As Long
    Dim msgText As String
    msgText = "Empty cells: "
    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
            i = 0
            Do While Cells(currentCell.Row + i, testCol).Interior.Color = RGB(255, 245, 217)
                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
                            If Len(msgText) Mod 50 = 0 Then
                                msgText = msgText & vbCrLf & Cells(currentCell.Row + i, j).Address & " "
                            Else
                                msgText = msgText & Cells(currentCell.Row + i, j).Address & " "
                            End If
                        Case Else
                            ' do nothing
                    End Select
                Next j
                i = i + 1
            Loop
        End If
    Next
    msgText = msgText & vbCrLf & "Total cells was " & count
    MsgBox msgText
End Sub

Thanks, I appreciate the revision in the code. I'll take a look and if I have any other questions, I'll let you know. Thanks for helping me out even if we are different parts of the world. You rock!

 

[myall_blues]

You’re welcome. Thanks for the feedback.

 

[TheTiredEngineer]

You’re welcome. Thanks for the feedback.

Hey so I tried manipulating the code for more rows and that worked great, but then when trying to change the number of columns to more or start from earlier columns, the count always gives me zero. So for example, Ive got information in E thru O, so thats 5 to 15. Ive tried using

For j = 5 To 15 Step 2

but havent had any luck. Unfortunately, I cant share screenshots of the actual tool this code will be used in, but the screenshots Ive been sharing is a similar style format. Theres data in many columns and while the code you provided worked great for columns 7 to 9 expanding on that wont work for some reason. Ive tried changing that loop to

For j = 5 To 15 Step 1

with no luck either. Any suggestions?

 

[myall_blues]

I'll have a look and get back to you.

 

[myall_blues]

When I run the code as supplied at post #13, with line updated to For j = 5 To 15 Step 2 for configuration below, I get correct cell addresses and answer of 10 cells.

Similarly if I update to just For j = 5 To 15 it also gives the correct answer. Did you change something else?
As long as column G (or whatever column corresponds to the value of the variable TestCol) has a complete set of yellow cells then it will work.

 

[TheTiredEngineer]

When I run the code as supplied at post #13, with line updated to For j = 5 To 15 Step 2 for configuration below, I get correct cell addresses and answer of 10 cells.
View attachment 115123

Similarly if I update to just For j = 5 To 15 it also gives the correct answer. Did you change something else?
As long as column G (or whatever column corresponds to the value of the variable TestCol) has a complete set of yellow cells then it will work.

Woops! I had changed to Step 1 and forgot to put it back to Step 2. I dont know why it didnt work yesterday but it looks like its working today. Thanks for looking into it.

 

[TheTiredEngineer]

The test sheet code works to an extent, but when I apply it to my actual sheets I run into some issues.

My sheet has sometimes 1 column in between and sometimes 2 columns in between (like below) and its miscounting. Should be 14 but it reads as 4. Also when the testCol doesnt always have a yellow cell it doesnt count any of the cells in the range. Like Rows 15 thru 20 are being skipped. I tried setting testCol to 2 but it was still miscounting.