Page 1 of 2 12 LastLast
Results 1 to 10 of 11

COUNTIF color = red?

This is a discussion on COUNTIF color = red? within the Excel Questions forums, part of the Question Forums category; I want to use a sumif and/or countif function in a spreadsheet, but I want it to associate with the ...

  1. #1
    Board Regular
    Join Date
    Jun 2002
    Posts
    162

    Default COUNTIF color = red?

    I want to use a sumif and/or countif function in a spreadsheet, but I want it to associate with the color of the text. For example, countif text is red (not ="red" but red in color). I know I can do this with code in VBA, but looking to keep it simple for another user. Can excel differentiate between text color in functions?

    Thanks in advance!

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,948

    Default

    Not without getting VBA involved
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default

    I'm not aware of any non-VBA solution, either macro or UDF.
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Posts
    2,317

    Default

    If you paste this into a module, you can use the formula

    =SumColor(A1,A2:A10)

    (A1 is the colour of cell you want to add)

    Function SumColor(rColor As Range, rSumRange As Range)

    Dim rCell As Range
    Dim iCol As Integer
    Dim vResult

    iCol = rColor.Interior.ColorIndex

    For Each rCell In rSumRange
    If rCell.Interior.ColorIndex = iCol Then
    vResult = WorksheetFunction.Sum(rCell) + vResult
    End If
    Next rCell

    SumColor = vResult
    End Function

  5. #5
    Board Regular
    Join Date
    Jun 2002
    Posts
    162

    Default

    A user-defined function would probably work. The one above will not work as it is looking at the interior rather than font color. I also will need to tweak to COUNT rather than SUM. Can I change interior to FONT and have it work? Trying now...

  6. #6
    Board Regular Iridium's Avatar
    Join Date
    Jul 2002
    Location
    West Bridgford, Notts., England
    Posts
    2,834
    Iridium


  7. #7
    Board Regular
    Join Date
    Jun 2002
    Posts
    162

    Default

    BEAUTIFUL!

    Thanks so much!

  8. #8
    Board Regular IsabellaLearning's Avatar
    Join Date
    May 2009
    Location
    Wellington NZ
    Posts
    65

    Default Re: COUNTIF color = red?

    JimBoy(or anyone listening

    the code you attached, can it be used with 2 colors eg: on the smae row i have blue for sic days and red for annual leave days - can i have two cells to the right one counting sick days only and the other annual leave only for the same line?

    regards
    Isabella

    Quote Originally Posted by jimboy View Post
    If you paste this into a module, you can use the formula

    =SumColor(A1,A2:A10)

    (A1 is the colour of cell you want to add)

    Function SumColor(rColor As Range, rSumRange As Range)

    Dim rCell As Range
    Dim iCol As Integer
    Dim vResult

    iCol = rColor.Interior.ColorIndex

    For Each rCell In rSumRange
    If rCell.Interior.ColorIndex = iCol Then
    vResult = WorksheetFunction.Sum(rCell) + vResult
    End If
    Next rCell

    SumColor = vResult
    End Function

  9. #9

    Join Date
    Oct 2006
    Posts
    2,541

    Default Re: COUNTIF color = red?

    =SumIfColours(A1:C100,3,A1,C1)
    where
    3 = ColRef to be summed within a range, (3rd column)
    A1,C1 have cell color to be summed.
    Code:
    Function SumIfColours(rng As Range, ref As Long, _
                    ref1 As Range, ref2 As Range) As Double
    Dim r As Range, col1 As Long, col2 As Long
    col1 = ref1.Interior.ColorIndex
    col2 = ref2.Interior.ColorIndex
    ref = ref - 1
    Application.Volatile
    For Each r In rng.Col(1).Cells
        If (r.Interior.ColorIndex = col1) + (r.Interior.ColorIndex = col2) Then
            SumIfColours = SumIfColours + r.Offset(, ref).Value
        End If
    Next
    End Function
    Note: If you change the colour in the cell, you need to hit F9 to update the result.

  10. #10
    New Member
    Join Date
    Jul 2011
    Posts
    5

    Default Re: COUNTIF color = red?

    Hi, I'm a bit new to VB so I might be doing something really stupid but would appreciate any help. I've gone to http://www.cpearson.com/excel/colors.htm and copied + pasted the =SumColor() function into VB editor in excel. I've then set up some dumby numbers in a column and randomly coloured some of them red. When I then use the syntax for the function in the example below the code on cpearson's page I just get a "#NAME" error.

    Can anyone point me in the right direction?

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com