Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Count the shaded cells

  1. #1
    Join Date
    5th May 2006
    Location
    Texas, USA
    Posts
    366

    Count the shaded cells

    Hello all
    What is a formula I can use to count the number of cells that have a "fill color" There are cells with no background color but I'm wanting to count the shaded ones.
    Thanks in advance

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Count the shaded cells

    Hi, This is a solution from the "Similar Threads" above:

    Count or Sum Cells by Their Fill Color

    --------------------------------------------------------------------------------

    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;
    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    5th May 2006
    Location
    Texas, USA
    Posts
    366

    Re: Count the shaded cells

    I see.
    Thank you.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Don't print shaded cells.
    By Onmyown in forum EXCEL HELP
    Replies: 4
    Last Post: June 13th, 2006, 01:48
  2. Function to count shaded cells in columns
    By NewToXL_VBA in forum EXCEL HELP
    Replies: 4
    Last Post: December 8th, 2005, 06:08

Bookmarks

Posting Permissions

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