Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Apr 2016
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel count by color

    Hi everyone, helpful posts here.

    I have a spreadsheet where many different lines are formatted with different colors. Does anyone know how to do a row count by color in excel?

    Thanks..
    Last edited by Midge; 2016-05-20 at 08:17.
    I'm losing weight with https://skinnyexpress.com/phen375-review this Phen375.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    These two very slick functions by C. Pearson can be placed in a module to identify the color of the background or the font.

    =COLORINDEXOFONECELL(A1,FALSE,1)

    Clip0001.jpg

    Clip0002.jpg

    Then, you can count occurrences of the color index number.

    Code:
        Function ColorIndexOfOneCell(Cell As Range, OfText As Boolean, _
            DefaultColorIndex As Long) As Long
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' ColorIndexOfOneCell
        ' This returns the ColorIndex of the cell referenced by Cell.
        ' If Cell refers to more than one cell, only Cell(1,1) is
        ' tested. If OfText True, the ColorIndex of the Font property is
        ' returned. If OfText is False, the ColorIndex of the Interior
        ' property is returned. If DefaultColorIndex is >= 0, this
        ' value is returned if the ColorIndex is either xlColorIndexNone
        ' or xlColorIndexAutomatic.
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim CI As Long
        
        Application.Volatile True
        If OfText = True Then
            CI = Cell(1, 1).Font.ColorIndex
        Else
            CI = Cell(1, 1).Interior.ColorIndex
        End If
        If CI < 0 Then
            If IsValidColorIndex(ColorIndex:=DefaultColorIndex) = True Then
                CI = DefaultColorIndex
            Else
                CI = -1
            End If
        End If
        
        ColorIndexOfOneCell = CI
        
        End Function
    
        Private Function IsValidColorIndex(ColorIndex As Long) As Boolean
            Select Case ColorIndex
                Case 1 To 56
                    IsValidColorIndex = True
                Case xlColorIndexAutomatic, xlColorIndexNone
                    IsValidColorIndex = True
                Case Else
                    IsValidColorIndex = False
            End Select
        End Function
    Last edited by kweaver; 2016-04-16 at 10:49.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2011
    Posts
    112
    Thanks
    35
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by Midge View Post
    I have a spreadsheet where many different lines are formatted with different colors.
    Hi Midge & Welcome to the Forum,

    My first question, how did the lines get formatted in the first place?

    If manually, then you will need some type of UDF as already provided; however, if by some criteria, then use that same criteria to perform a count.

Posting Permissions

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