Count no'of empty cells with no color fill

[adiles]

Hi all,

I am looking for a formula which will count the no of blank cells in a column having no color fill.
I have values in both filled and unfilled cells. i want to get the count of unfilled cells if there is no value in it.

 

[Special-K99]

I haven't touched Excel for a few years so this answer may not be up to date.
A few years back the only way to detect colour in a cell was by VBA. And you've stated you want a formula.
Unless things have changed that won't be possible.

 

[HighAndWilder]

Hi all,

I am looking for a formula which will count the no of blank cells in a column having no color fill.
I have values in both filled and unfilled cells. i want to get the count of unfilled cells if there is no value in it.

Put this function into a standard code module and call it like this in a cell for example:

=fncCountCellsByColor(A1:A200)

where A1:A200 is the range that you are searching in.

If you want to search column A for example it will search from cell A1 to the last cell in the column containing a value.

e.g. =fncCountCellsByColor(A:A)

Public Function fncCountCellsByColor(data_range As Range) As Long
Dim cellCurrent As Range
Dim cntRes As Long

  Application.Volatile
  
  cntRes = 0
  
  With data_range
  
    If .Columns.Count = 1 And (.Rows.Count = Cells.Rows.Count) Then
      
      Set data_range = Cells(1, data_range.Column).Resize(Cells(Rows.Count, 1).End(xlUp).Row, 1)
    
    End If
  
  End With
  
  For Each cellCurrent In data_range
    
    With cellCurrent.Interior
    
    If (.Pattern = xlNone) And _
        (.TintAndShade = 0) And _
        (.PatternTintAndShade = 0) Then
    
      cntRes = cntRes + 1
    
    End If
    
    End With
  
  Next cellCurrent

  fncCountCellsByColor = cntRes
 
End Function