Count Cells Based On Background Color

Re: Count Cells Based On Background Color By Formula

What do you mean by

Quote

I can't to do it in VBA.

?

Re: Count Cells Based On Background Color By Formula

He means he can't use VBA to do it - he wants a formula. Must be VBA though, I think. CELL won't work.

Re: Count Cells Based On Background Color By Formula

"Excel does not have any built-in worksheet functions for working with the colors of cells or fonts. If you want to read or test the color of a cell, you have to use VBA procedure."

http://www.cpearson.com/excel/colors.htm

Re: Count Cells Based On Background Color By Formula

Flee,
This solution DOES have vba in the form of a UDF but you can set it up and the user of the spreadsheet won't ever need to use VBA. So I am hoping that will suffice. I found this in another thread.

"Want to Sum or Count cells that have a specified Fill Color? This can be done with the aid of a Custom Function. A custom function, also called a User Defined Function, is a function that we write ourselves using VBA (Visual Basic for Applications). Here is how.

First open the Workbook in which you wish to count or sum cells by a fill color. Now go into the Visual Basic Editor via Tools>Macro>Visual Basic Editor (Alt+F11) and then, from within the Visual Basic Editor go to Insert>Module to insert a standard module. Now, in this module, enter in the code as shown below;

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

You can now use the custom function (ColorFunction) like;

=ColorFunction($C$1,$A$1:$A$12,TRUE)
to SUM the values in range of cells $A$1:$A$12 that have the same fill color as cell $C$1. The reason it will SUM in this example is because we have used TRUE as the last argument for the custom function.

To COUNT these cells that have the same fill color as cell $C$1 you could use:
=ColorFunction($C$1,$A$1:$A$12,FALSE)
or =ColorFunction($C$1,$A$1:$A$12)
by omitting the last argument our function will automatically default to using FALSE.

***Be aware that the changing of a cells fill color will not cause the Custom Function to recalculate (unless you use Paste Special - Formats), even if you press F9 (Recalculates the whole Workbook). You will need to either, select the cell and re-enter the formula, or go to Edit>Replace and replace = with =, or use Ctrl+Alt+F9 ***

Try also to avoid the use of Application.Volatile as it will not help in this case and only slow down Excel's calculation time.

See Also: 2 Separate Functions Here and Excel Sort by Color
__________________
Kind Regards
Dave Hawley
Software for Excel & Standalone
Ozgrid Excel Consulting - Free Quotes
EXCEL"

HTH

edit: didn't see the link Sicarii attached. Same thing i presume, though I just perused the site.

Re: Count Cells Based On Background Color

[INDENT]hi,
could you please answer me the bellow formula how its look like.. if its possible in using excel formula or using VBA.
I want to summerize white color cell in column A1 to A100 which values are negative (in between a1 to a100 there are diffrent color cells present. so need to summerize the white cell only with condition which are negative). [/INDENT]