Search for Cells of certain color and empty

[TheTiredEngineer]

Hello

Im trying to get my excel vba to search for all the cells that are filled with a tan color and then find within those the cells that are empty. So far its been listing all the cells that are filled with a tan color and ignoring my code to check for only empty cells. Some cells are supposed to have numbers, and some are text. Any help is appreciated.

Option Explicit

Dim MyRange, MR, rngCell, rngCol As Range
Dim rngCount, TotalBlanks As Long
Dim rngCellValue As String


Sub Button19_Click()
  
    Set MR = Range("A1:AF184")
    
    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 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 rngCell.Value <> vbNullString Then 'checks if a cell is blank
            
            TotalBlanks = TotalBlanks + 1
                
                Debug.Print rngCell.Address & " is blank"
                Debug.Print TotalBlanks
                'Debug.Print rngCount
                'Debug.Print rngCell.Value
            End If
        End If
    Next rngCell
End Sub

 

[jkpieterse]

I think you'll find this works better:

Change this:

If rngCell.Value <> vbNullString Then

to this:

If len(rngCell.Formula) =0 Then

 

[RobP]

Hi,

I'm not quite clear, but if you wanted to count blanks, would you not look for an "=" rather than a "<>" ?

If rngCell.Value <> vbNullString Then 'checks if a cell is blank

this suggests to increment your count if the cell is NOT blank ?

might help

Rob

 

[TheTiredEngineer]

I think you'll find this works better:

Change this:

If rngCell.Value <> vbNullString Then

to this:

If len(rngCell.Formula) =0 Then

It looks like that worked. But then if I want to pair that with if each drop down is Y or N, to only check for those blanks if Y, how do I do that? 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.