Countif / Cell Function

  • Thread starter Thread starter JimmyJumbo
  • Start date Start date
J

JimmyJumbo

Hi
I am trying to count the number of occasions a particular
format occurs in and array, for instance L4:R32. At
present I am trying to count cells shaded dark grey, but
could count a partiuclar number format so long as I can
still enter positive numbers or text without it being
affected. The CELL function seems to produce a useful
result, giving teh answer 1 using a negative number format
with the "color" info type specified, but I cannot work
out how to get it to do this calculation on all the cells
in the array and count / sum the total.
Any help much appreciated.

JJ
 
Try this (copy to a macro sheet). Put a formula in a cell like :-
=greycount(L4:R32)

'-------------------------------------
Public Function GreyCount(MyRange As Range)
GreyCount = 0
For Each c In MyRange.Cells
If c.Interior.ColorIndex = 15 Then
GreyCount = GreyCount + 1
End If
Next
End Function
'-------------------------------------
 
Back
Top