Count using Color Function

[sjm44]

Hello,

I am using Excel 2010 and I'm trying to figure out how to count the number of cells that are filled with a certain colour.

I have browsed the forums and came across a thread on Color Function. That thread told me to use the following code.

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)

Dim rCell As Range

Dim lCol As Long

Dim vResult

lCol = rColor.Interior.ColorIndex



If SUM = True Then

For Each rCell In rRange

If rCell.Interior.ColorIndex = lCol Then

vResult = WorksheetFunction.SUM(rCell, vResult)

End If

Next rCell

Else

For Each rCell In rRange

If rCell.Interior.ColorIndex = lCol Then

vResult = 1 + vResult

End If

Next rCell

End If



ColorFunction = vResult

End Function

I am also using the following formula,

=ColorFunction(C1,A1:A22,TRUE)

At the moment when I try to run the formula I get the following message in the code for Module 2, "Complie error: Invalid outside procedure"

I feel I am some way off at the moment and could use a little help to determine how many cells are filled with a certain colour.

Any help would be much appreciated.

Thanks.

 

[VoG]

If you want to count rather than sum cells you need to change TRUE in the formula to FALSE.

That function will only count manually coloured cells, not cells coloured by Conditional Formatting.

 

[sjm44]

If you want to count rather than sum cells you need to change TRUE in the formula to FALSE.

That function will only count manually coloured cells, not cells coloured by Conditional Formatting.

Ok, so now I have the following...

=ColorFunction(E5,A1:A22,FALSE)

The cells I want to count are filled by using the 'Fill' icon, every time I click away from the formula though it takes me to the Compile Error.

Do I still need to have the Modules if I have this formula?

 

[VoG]

You either need to have that code in a regular module in each workbook that you want to use it in, or store it in your Personal workbook http://www.rondebruin.nl/personal.htm

 

[sjm44]

You either need to have that code in a regular module in each workbook that you want to use it in, or store it in your Personal workbook http://www.rondebruin.nl/personal.htm

Sorted.

Thanks a lot.