Results 1 to 8 of 8

Thread: Countif

  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Countif

    I want to do a countif based on the color of a cell. At the bottom of a column, I want to know how many cells are red, how many are blue, how many are green, etc. Any ideas? I suppose I'll have to resort to a user defined function with VBA?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Countif

    Do the cells derive their colour from conditional formatting ?. If so could you base you COUNTIF on the same criteria. Otherwise a VBA procedure would be required. What do mean by cell colour, Font or Shading ?.

    Andrew C

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Countif

    Thanks Andrew. The cells are not given their color by conditional formatting. By cell color, I'm referring to the fill color (icon of a paint bucket). I can write the VBA code if I need to but I don't know how to create a user defined function.

  4. #4
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Countif

    I figured out how to make my own function. I just recorded a Macro and then edited its name to say Function instead of Macro. Now I'm playing with it to get it to do what I want. I don't know if I'll be succesful but I'll give it my best shot.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Countif

    Put the following code in a module


    Function CountColors(R As Range, Col As Integer) As Integer
    Dim cell As Range
    CountColors = 0
    For Each cell In R
    If cell.Font.ColorIndex = Col Then
    CountColors = CountColors + 1
    End If
    Next
    End Function

    Then via the function wizard, select user defined and you should find the function CountColors in the list. This function requires you to enter the range in which you want to count the colors and the integer value representing the color (e.g. Red = 3). See the help for colorindex to find out the colors and the integers that correspond.

  6. #6
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Countif

    Thanks a million. That worked perfectly. The only thing that strikes me funny is that the 'cell' value in the 'Range' within the For..Next loop is not B2 or C8 or whatever, but rather the number that happens to be in that cell. It works though so I'm thrilled.

  7. #7
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Countif

    Actually, I did have to make one change to your code. One line needs to read "If cell.Interior.ColorIndex = Col Then" instead of cell.Font.ColorIndex. Thanks again.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Countif

    In that routine, cell is a range object. Its value property is whatever displays in the active cell in the range. If you want to see the address of the range, then you would need to look at it Address property (cell.Address).
    Legare Coleman

Posting Permissions

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