Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Apr 2016
    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?

    Last edited by Midge; 2016-05-20 at 07:17.
    I'm losing weight with this Phen375.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    La Jolla, CA
    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.




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

        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
            CI = Cell(1, 1).Interior.ColorIndex
        End If
        If CI < 0 Then
            If IsValidColorIndex(ColorIndex:=DefaultColorIndex) = True Then
                CI = DefaultColorIndex
                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 09:49.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2011
    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