SUM/COUNT CELLS BY FILL COLOUR

[josephm]

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.

[VBA]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[/VBA]

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.

[Bob Phillips]

You have called the function ColorFunction in the code, but tried to use ColourFunction in the spreadsheet. They have to be the same.

[josephm]

thank you i am so foolish. will it work for font color or that requires change in the code? because it will be dates in the cells in the specified range that are green

i corrected the function name and used the following formula on sheet2 but still got the #NAME error

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

in cell A1 of Sheet1 i formatted the cell to font color green but the cell is empty initially do i have to put anything in cell A1.
is it that this formula must be used on the same sheet as the range or am I doing the sheet reference wrong?

[Bob Phillips]

Yes if you change the line

[vba]

If rCell.Interior.ColorIndex = lCol Then
[/vba]

to

[vba]

If rCell.Font.ColorIndex = lCol Then
[/vba]

[josephm]

were u able to get this to work on the sheet i provided?
i made the change in the code as follows...

[VBA]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.Font.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Font.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If

ColorFunction = vResult
End Function[/VBA]


then in cell B2 of Sheet2 i placed the following formula without absolute references

=ColorFunction(Sheet1!A1,Sheet1!B3:Sheet1!CS3,TRUE)

and it returened 0.

on sheet1 in cell A1 i have nothing at all no characters of any kind but when u type in the cell whatever u type will turn green.

now with in the range of cells to be checked they are green dates in the format of 4-Feb-08 for example.

i have column A freezed will this affect things?

[Zack Barresse]

Joseph, [uvba]use em please[/uvba]. I've done this for you on your posts.

[Bob Phillips]

Yes I did, I got 39636. I think you should be using

=ColorFunction(Sheet1!A1,Sheet1!B3:Sheet1!CS3,FALSE)

because you want to Coount not Sum.

[josephm]

=ColorFunction(Sheet1!A1,Sheet1!B3:Sheet1!CS3,FALSE)

i used the above formula and i got #name err

[josephm]

[vba]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.Font.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Font.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If

ColorFunction = vResult
End Function[/vba]

is the syntax bolded above correct or should it be

[VBA] lCol rCell.Font.ColorIndex[/VBA]

[josephm]

thank you zack.

[Bob Phillips]

?????????

[Zack Barresse]

If you are getting the #NAME error, are you sure you have the function in the correct module??