count blank cells by colour

[Peter Davison]

Is it possible to count blank cells that have a specific colour

 

[Jimmypop]

Is it possible to count blank cells that have a specific colour

Something like the below maybe... Currently using this...

'Code created by Sumit Bansal from https://trumpexcel.com
Function GetColorCount(CountRange As Range, CountColor As Range)
Dim CountColorValue As Integer
Dim TotalCount As Integer
CountColorValue = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
  If rCell.Interior.ColorIndex = CountColorValue Then
    TotalCount = TotalCount + 1
  End If
Next rCell
GetColorCount = TotalCount
End Function

 

[bebo021999]

I test with blank cells, with colour = vbYellow

Option Explicit
Sub countcolor()
Dim rng As Range, cell As Range, count&
Set rng = ActiveSheet.UsedRange
For Each cell In rng.SpecialCells(xlCellTypeBlanks)
    If cell.Interior.Color = vbYellow Then count = count + 1
Next
msgbox  count
End Sub

 

[Peter Davison]

That's great.
Is there a cell formula available without using VBA?

 

[Jimmypop]

My guess is you would have to use something in the range of Named Ranges and GET.CELL (might be wrong) but here is a link to using GET.CELL...

How to count and sum cells based on background color in Excel?

In this tutorial, you can get some tricks on counting or summing cells based on the background color in Excel.

www.extendoffice.com

 

[Fluff]

Is there a cell formula available without using VBA?

Fraid not, you will need to use VBA. Either as a macro, or as as UDF that you can call from a cell.

 

[Peter Davison]

Thank everyone for all your help.
Very appreciated