# Excel Function to exclude count of cells that has a different fill color

I need a code to count only the cells in a worksheet that:

a) is equal or greater than 75
AND
b) exclude those that has a fill color of gray
Please see attached worksheet as sample.

Sample-Test-for-Count.xls
###### Who is Participating?

Commented:
Ok this one has three parameters

function(range, must be greater than or equal to number, and colorindex for exclusion)

e.g.,  =CountSkipColor(A1:A5,75,15) - checks range A1-A5, must be >= 75 to count, and must not be colorindex 15 (grey)
Function CountSkipColor(rRange As Range, minOver As Double, colorToCheck As Double) As Long
Dim myCell As Range, countcolor As Long

Application.Volatile
For Each myCell In rRange
If myCell.Interior.ColorIndex <> colorToCheck And myCell.Value >= minOver And IsNumeric(myCell.Value) Then 'not grey and over minimum amount
countcolor = countcolor + 1
End If

Next myCell

CountSkipColor = countcolor
End Function

PS - also added the function getColorIndex(range) returns the color index of a cell you point to (or first cell in a range you point to) so that should help for future....

Function getColorIndex(rRange As Range) As Long

getColorIndex = rRange.Cells(1, 1).Interior.ColorIndex
End Function
Enjoy!

Dave
Sample-Test-for-Count-r3.xls
0

Commented:
Here is a sample.

Code Used

Public Function MyTot(rng As Range) As Long
Application.ScreenUpdating = False
Application.Volatile
Dim cl As Range, tot As Long

For Each cl In rng
If IsNumeric(cl.Value) Then
If cl.Interior.ColorIndex <> 15 And (cl.Value = 75 Or cl.Value > 75) _
Then tot = tol + 1
End If
Next
MyTot = tot
Application.ScreenUpdating = True
End Function
Total-Example.xls
0

Author Commented:
@SiddharthRout:  Thanks for the code but when I do it manually, the count should be 4.  ~thanks.

0

Commented:

Also, attached solution worksheet:

Enjoy!

Dave
Function CountSkipGrey(rRange As Range, minOver) As Long
Dim myCell As Range, countcolor As Long

For Each myCell In rRange
Debug.Print myCell.Value, myCell.Interior.ColorIndex
If myCell.Interior.ColorIndex <> 15 And myCell.Value >= minOver Then 'not grey and over minimum amount
countcolor = countcolor + 1
End If

Next myCell

CountSkipGrey = countcolor
End Function
Sample-Test-for-Count-r1.xls
0

Commented:
@sid - didn't know you posted and I was too slow (I was interrupted !) lol

@ emacyam

This one works!!!
Dave
0

Commented:
Sorry there is a typo

Replace

Then tot = tol + 1

by

Then tot = tot + 1

Sid
0

Author Commented:
@Sidd:  The replace quote is the same....  thanks.
0

Commented:
@emacyam - mine works, dude!

See post 35033149, just above :)

Dave
0

Commented:
No it is not ;)

the 1st one has tol (notice the L)

Sid
0

Commented:
@sid - you got me on the isnumeric.  I was "there", but got interrupted then plunged ahead and missed it....

lol - we working too fast between type-o's and interruptions....

Dave

Here's my updated...
Function CountSkipGrey(rRange As Range, minOver) As Long
Dim myCell As Range, countcolor As Long

For Each myCell In rRange
If myCell.Interior.ColorIndex <> 15 And myCell.Value >= minOver And IsNumeric(myCell.Value) Then 'not grey and over minimum amount
countcolor = countcolor + 1
End If

Next myCell

CountSkipGrey = countcolor
End Function
Sample-Test-for-Count-r2.xls
0

Author Commented:
@dlmille:  your solution worked but when I did some tests (add another number greater than 75 in a cell and applied gray fill color), the formula counted that cell.

@Sidd:  thanks, your solution worked.  Thing is, is there a way to recalculate without saving the document first and reopen?  Is there a hot key?

Thanks to both of you :)
0

Commented:
Apologies on that.

Try once again!

Dave
Sample-Test-for-Count-r2.xls
0

Commented:
So the function I created allows you to select a range to count, and a number it can be > or = to to include in the count.

Dave
0

Commented:
And you have to use the EXACT same color of GREY - we're both keying in on colorindex = 15....

Dave
0

Commented:
Yes.

You have to calculate the formula again.You can press F2 where the formula is so that you don't need to restart.

Sid
0

Author Commented:
GREAT!  Worked like a charm on both solutions :-)  thanks a bunch!

@dlmille:  just to understand your formula, what does the "75" do? =countskipgrey(B2:B35,75)
0

Commented:
you can change the 75 to any number that marks your MINIMUM.  E.g., GREY is hard coded, but 75 you can change what the bottom limit is for exclusion or inclusion

Dave
0

Author Commented:
@dlmille:  cool!  I like that :)
0

Commented:
Would you like me to add a variable for the COLOR to check as well?

Dave
0

Commented:
emacyam

http://www.mvps.org/dmcritchie/excel/colors.htm

for the color index ;)

Sid
0

Author Commented:
@dlmille:  can you please repost the code from the last sample so I an accept that solution as well....

@sidd and dlmille:  will split the points... hope its ok...
0

Commented:
It is okay with me ;)

Sid
0

Commented:
Sounds good to me - coming momentarily
0

Author Commented:
@Sidd:  thanks a bunch... that link is great!

@Dave:  why not?  that variable will come in handy for sure :)
0

Author Commented:
Both experts were very helpful. A+++ all the way!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.