Saturday, March 7, 2009

Counting cells based on interior color of the cell

Paste the code in vba standerd module in Excel:

Public Function CountColorCode(rng As Range, cRange As Range) As Integer

Dim I As Integer

For Each Cell In rng
If Cell.Interior.ColorIndex = cRange.Interior.ColorIndex Then
I = I + 1
End If

Next

CountColorCode = I

End Function

------------Testing the function-----

Syntax rng As Range = CountColorCode(rng, cRange)

Rng is Range where we need to look for colored cells and count them
cRange s the criteria range which needs to be searched

eg: we need to count the cells in range “A1:B20” which color same as cell D1
Then the formula is = CountColorCode(A1:B20, D1)

No comments:

Post a Comment