+ Reply to Thread
Results 1 to 5 of 5

How to count colored cells per each cell range?

  1. #1
    Registered User
    Join Date
    11-11-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    46

    How to count colored cells per each cell range?

    Hi,

    What macros is needed to count the number of yellow cells per each cell range? The attached Excel contains the sample data plus how the results should look after query.


    Is there an alternative way to macros to get these results?

    Thank you,
    Gabriele
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    04-09-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    391

    Re: How to count colored cells per each cell range?

    Hi,

    See the file, it uses UDF.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    08-04-2014
    Location
    Riga
    MS-Off Ver
    2010
    Posts
    579

    Re: How to count colored cells per each cell range?

    You can insert this code in 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.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

    Then use this formula to count cells =colorfunction($B$1;B1:P1;FALSE) or this to sum the values =colorfunction($B$1;B1:P1;TRUE)

    $B$1 is the cell which has the color you need.
    B1:P1 is the cell range

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,686

    Re: How to count colored cells per each cell range?

    Please Login or Register  to view this content.
    see the attached file
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  5. #5
    Registered User
    Join Date
    11-11-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: How to count colored cells per each cell range?

    Thank you, Siva, it works nicely.
    Best,
    Gabriele

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 11-09-2013, 02:43 AM
  2. COUNT (or SUM?) colored cells
    By beanpoddy in forum Excel General
    Replies: 2
    Last Post: 03-27-2012, 01:14 PM
  3. Excel 2007 : Count colored cells in vba
    By nlm in forum Excel General
    Replies: 4
    Last Post: 08-25-2011, 07:58 AM
  4. How to Count only Colored cells
    By WM_1956 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-26-2005, 09:35 AM
  5. [SOLVED] Count non-colored cells
    By Ken G in forum Excel General
    Replies: 3
    Last Post: 01-02-2005, 09:07 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1