+ Reply to Thread
Results 1 to 6 of 6

help on color counting -

Hybrid View

crazydude80 help on color counting - 04-16-2012, 09:52 AM
crazydude80 Re: help on color counting - 04-17-2012, 04:39 AM
nilem Re: help on color counting - 04-17-2012, 05:22 AM
watersev Re: help on color counting - 04-17-2012, 05:35 AM
jindon Re: help on color counting - 04-17-2012, 06:07 AM
crazydude80 Re: help on color counting - 04-17-2012, 08:01 AM
  1. #1
    Registered User
    Join Date
    04-14-2012
    Location
    perth
    MS-Off Ver
    Excel 2003
    Posts
    25

    help on color counting -

    Hi,

    Need help on below code. If u refer to the pic attached, i need to input the condition at an empty cell to count certain color. Is there a way to count "all the colors" without need to conditioning it ? Maybe with a button click will display all the colors with the counts ??



    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

    CUrrent image:

    color2.JPG
    Last edited by crazydude80; 04-17-2012 at 04:38 AM.

  2. #2
    Registered User
    Join Date
    04-14-2012
    Location
    perth
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: help on color counting -

    any pro's on these ? thanks

  3. #3
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: help on color counting -

    may be something like
    Sub ert()
    Dim k, i&, clr&, rng As Range, r As Range
    Set rng = [a1:a20]
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For Each r In rng
            clr = r.Interior.Color
            .Item(clr) = .Item(clr) + 1
        Next r
        For Each k In .keys
            i = i + 1
            Cells(i, 4).Interior.Color = k
            Cells(i, 4).Value = .Item(k)
        Next k
    End With
    End Sub
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: help on color counting -

    hi crazydude80, option, please check attachment, select cells and press the button
    Attached Files Attached Files
    Last edited by watersev; 04-17-2012 at 05:54 AM.

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835

    Re: help on color counting -

    UDF
    1) Select 2 horiaontal consecutive cells. e.g G1:H1
    2) click on the formula bar and paste the formula
    =CountAllColors($A$1:$E$11,ROWS(A$1:A1))
    (change $A$1:$E$11 accordingly.)
    then confirm with Ctrl + Shift + Enter (Array formula entry)
    3) drag those 2 cells down
    Col.G are ColorIndex
    Col.H are occurence
    Function CountAllColors(rng As Range, ref As Long) As Variant
    Dim r As Range
    With CreateObject("System.Collections.SortedList")
        For Each r In rng
            .Item(r.Interior.ColorIndex) = _
            .Item(r.Interior.ColorIndex) + 1
        Next
        CountAllColors = Array(.GetKey(ref - 1), .GetByIndex(ref - 1))
    End With
    End Function

  6. #6
    Registered User
    Join Date
    04-14-2012
    Location
    perth
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: help on color counting -

    great .. thank you guys.. it works !!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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