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.