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]

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

Just following up to my last post to see if you may have suggestions for getting the code to work?

 

[myall_blues]

Just following up to my last post to see if you may have suggestions for getting the code to work?

I didn't see it I'm sorry. Generally once a post has been marked as answered it's best to start a new thread, especially because it will attract attention from others. As a rule I ignore any solved questions.

Now to the issue at hand - it doesn't surprise me it's not working because your actual case really looks nothing like any of the examples posted previously. The biggest issue is the inconsistency, especially the lack of any column that can be relied on to have consistent content. If you could modify it so that, for example, every relevant row in column B had a Y or an N in it (i.e in this case put a Y or N in B6:B11 and B15:B20) then it becomes easy, but as shown I've tried several different ways without success.

.