josephm

06-11-2008, 06:25 AM

i am trying to use a custom function in excel to count the number of cells in a given range that are fill with a specific colour.

The range in question is one Sheet1 and the formula will be on Sheet2.

i used a module to create the custom function via the Visual Basic Editor.

i used 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

''''''''''''''''''''''''''''''''''''''

'Written by Ozgrid Business Applications

'www.ozgrid.com

'Sums or counts cells based on a specified fill color.

'''''''''''''''''''''''''''''''''''''''

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

On Sheet2 i placed the following formula but i got a #NAME error.

[/B]

=ColourFunction(Sheet1!$A$1,Sheet1!$B$3:Sheet1!$CS$3,TRUE)

Cell A1 in Sheet1 is filled with the colour the formula is to count by

the range is B3:CS3

i have attached the excel file i am testing this on.

The range in question is one Sheet1 and the formula will be on Sheet2.

i used a module to create the custom function via the Visual Basic Editor.

i used 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

''''''''''''''''''''''''''''''''''''''

'Written by Ozgrid Business Applications

'www.ozgrid.com

'Sums or counts cells based on a specified fill color.

'''''''''''''''''''''''''''''''''''''''

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

On Sheet2 i placed the following formula but i got a #NAME error.

[/B]

=ColourFunction(Sheet1!$A$1,Sheet1!$B$3:Sheet1!$CS$3,TRUE)

Cell A1 in Sheet1 is filled with the colour the formula is to count by

the range is B3:CS3

i have attached the excel file i am testing this on.