# Thread: Sums or counts cellsbased on specified fill color (2003)

1. ## Sums or counts cellsbased on specified fill color (2003)

I came across this user defined function but don't know how to use it. I placed it in a module in my Personal.xls sheet. I think it counts the cells that have a color but don't know how to change the code to designate a specific color to count etc..............Thanks for your help.

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult

''''''''''''''''''''''''''''''''''''''
'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

2. ## Re: Sums or counts cellsbased on specified fill color (2003)

You set the color of a cell to the color you want to count or sum and pass that cell as the first argument to the function.

3. ## Re: Sums or counts cellsbased on specified fill color (2003)

OK call me stupid - When you say "You set the color of a cell to the color you want to count or sum and pass that cell as the first argument to the function" - how do you do this? With conditional formatting? How do you use the function? Do you put it at the bottom of a column - =Personal.xls!ColorFunction(A1:A10) - what is the syntax? Can you give an example of how to use this function?

4. ## Re: Sums or counts cellsbased on specified fill color (2003)

To expand on Legare's reply: the formula

=Personal.xls!ColorFunction(F1,A130)

will count the number of cells in A130 with the same fill color as F1. So if you make cell F1 red, the formula will count red cells, and if you make F1 blue, the formula will count blue cells.

If you want to sum the cell values instead of count the cells, use

=Personal.xls!ColorFunction(F1,A130,TRUE)

6. ## Re: Sums or counts cellsbased on specified fill color (2003)

Yes, thank you both.

7. ## Re: Sums or counts cellsbased on specified fill co

How, if possible, would I change this function to sum cells where the content have a text colour? Or more to the point, not a text colour? We have a spreadsheet where amount unpaid are black and when paid they are changed to red text with strikethrough. I want to sum the black, i.e. unpaid, amounts.

David

8. ## Re: Sums or counts cellsbased on specified fill co

<pre>Function ColorFunction(rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim vResult

''''''''''''''''''''''''''''''''''''''
'Sums or counts cells based on the font not being red and struckout.
'''''''''''''''''''''''''''''''''''''''

If SUM = True Then
For Each rCell In rRange
With rCell.Font
If Not (.Color = vbRed And .Strikethrough = True) Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
End With
Next rCell
Else
For Each rCell In rRange
With rCell.Font
If Not (.Color = vbRed And .Strikethrough = True) Then
vResult = 1 + vResult
End If
End With
Next rCell
End If

ColorFunction = vResult
End Function
</pre>

It will sum anything that is not Red and struck out.

9. ## Re: Sums or counts cellsbased on specified fill co

Thanks for the response.

=Colorfunction(S39:Z39,-1) typed into a cell gives a #NAME? error. The error message in Excel2003 says "Invalid name error" and the function does not appear in the macros list. I've put this function in the worksheet's VBA module so it should show. Any ideas what I'm doing wrong?

David

10. ## Re: Sums or counts cellsbased on specified fill co

The function should go in a normal module in your workbook not in the worksheet's module. Select the workbook in the project explorer in the VBEditor, choose Insert-Module, then paste the code into the new module (removing it from the worksheet module). Note: Functions do not appear in the macro dropdown.
HTH

11. ## Re: Sums or counts cellsbased on specified fill co

Rory

This now works fine if I add or delete figures within the range. However, changing an amount from black to red/strikethrough does not force a recalculation, and neither does F9 (I'm set to recalc automatically). The only way seems to be to select the cell with the formula and go into it as if to edit it; exiting forces a recalc. This is impractical.

Any thoughts?

David

12. ## Re: Sums or counts cellsbased on specified fill co

You could use the worksheet selectionchange event but that's a bit overkill I think. Personally, I would add a column to your spreadsheet labelled Paid and simply put a Y (or Yes) in it when paid. You can then have conditional formatting set up on your numbers column so that if the corresponding cell in the Paid column contains a Y, it automatically sets the Font to Red and struck through. You can then also simply use a SUMIF formula to total paid or unpaid amounts.
HTH

13. ## Re: Sums or counts cellsbased on specified fill co

If you insert the line

Application.Volatile

at the beginning of the function, F9 will recalculate its result.

14. ## Re: Sums or counts cellsbased on specified fill co

Brilliant, thanks Rory and Hans

David

15. ## Re: Sums or counts cellsbased on specified fill color (2003)

Han s,
A new twist: Assume in column A of a worksheet, I have a list names, say 150, some of which I have formatted with different fill colors. In column B I have test scores for each respective name, but no fill color for any of these cells (I need to keep them "un-filled" for various reasons). At the bottom of column B, I would like to sum the scores for each name with a particular filled color in column A. For example, if there are 18 names in column A whose cell color is red, I would like to sum those respective scores at, say, cell B152. Any ideas?
As always, thanks!

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•