Counting coloured cells that are blank

[smilinglilies]

Hi

I have a spreadsheet that has different colours in. I need to be able to count all blank orange cells within a certain range.

Countblank doesn't work, so I thought of perhaps using countccolor as I can get it to count all the orange cells but then I don't know what to add to it to count just blank cells.

Could someone point me in the right direction please

Thank you

 

[Fluff]

Can you supply the code you have for countcolor

 

[smilinglilies]

Function CountCcolor(range_data As Range, criteria As Range) As Long
Dim datax As Range
Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
If datax.Interior.ColorIndex = xcolor Then
CountCcolor = CountCcolor + 1
End If
Next datax
End Function



Using the formula =countccolor(E14:AI17,G2) gives me 23 which is correct, I now need to know how many of these 23 orange cells have no data in them.

 

[Fluff]

OK, try

Function CountCcolor(range_data As Range, criteria As Range) As Long
Dim datax As Range
Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
   If datax.Interior.ColorIndex = xcolor And datax.Value = "" Then
      CountCcolor = CountCcolor + 1
   End If
Next datax
End Function

 

[smilinglilies]

Thank you - would I be able to use the old formula on the same spreadsheet at the same time as I use it to work out the availability overall - the new formula is to break it down into 2 and 3 bed villas and what is still available.

 

[Fluff]

You can use

Function CountCcolor(range_data As Range, criteria As Range, Optional ValU As String = "*") As Long
Dim datax As Range
Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
   If datax.Interior.ColorIndex = xcolor And datax.Value Like ValU Then
      CountCcolor = CountCcolor + 1
   End If
Next datax
End Function

Then this
=countccolor(E14:AI17,G2)
will count all, or this
=countccolor(E14:AI17,G2,"")
will count blanks

 

[smilinglilies]

Thank you - working great now!

 

[Fluff]

You're welcome & thanks for the feedback