Function that returns the total number of cells in a range i
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Function that returns the total number of cells in a range i

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a long range of cells in which the cell text is formatted in different colors. Has anyone created a custom function that can return the number of cells in a range that the text is formatted in a certain color (red, blue, black, etc.)?

    Thanks,
    Jim

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,827
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-02-23 13:08, jimi143 wrote:
    I have a long range of cells in which the cell text is formatted in different colors. Has anyone created a custom function that can return the number of cells in a range that the text is formatted in a certain color (red, blue, black, etc.)?

    Thanks,
    Jim
    What condition/criterion is used to format the text in a given cell as red?


  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The text color of the cell is user defined using the standard colors available in Excel. I would like to put a formula at the bottom of a long range of cells that counts the number of cells in the above range that have red text formatting.

    Thanks,

    Jim

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    I answered this heavly on the old boad with VBA to do just this:

    A friend Ivan F Moala edited it to colour of text .. have a look post answet by me and then Ivan, will be in first archive, the original code was by a great friend in Western Australia Dave Hawley http://www.ozgrid.com under vba section.

    Credit to he guys who did the real work, have a look at both, ill see if i can find the code as well



    _________________
    Good Luck
    HTH

    Rdgs
    ==========
    Jack in the UK

    [ This Message was edited by: Jack in the UK on 2002-02-24 14:27 ]

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,827
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-02-23 13:35, jimi143 wrote:
    The text color of the cell is user defined using the standard colors available in Excel. I would like to put a formula at the bottom of a long range of cells that counts the number of cells in the above range that have red text formatting.

    Thanks,

    Jim
    User-defined, meaning: user makes the text red without using any known/explicit criterion?

    Jim: If so, you'll need VBA-code to obtain desired counts. The Archives of the board has some code for similar situations.

    Aladin



    [ This Message was edited by: Aladin Akyurek on 2002-02-23 13:55 ]

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jimi,

    If you bury this in a normal module in your visual basic editor:

    Function SumByColor(InputRange As Range, ColorRange As Range) As Double
    Dim cl As Range, TempSum As Double, ColorIndex As Integer
    ColorIndex = ColorRange.Cells(1, 1).Font.ColorIndex
    TempSum = 0
    On Error Resume Next
    For Each cl In InputRange.Cells
    If cl.Font.ColorIndex = ColorIndex Then TempSum = TempSum + cl.Value
    Next cl
    On Error GoTo 0
    Set cl = Nothing
    SumByColor = TempSum
    End Function

    And use the following formula:

    =SumByColor(A3:A9,A1)

    Where the first paramter is the range to sum and the second is the range that has the font color you want to evaluate against.

    Hope this helps. Cheers,

    Nate



  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Put this into a normal module. Call the function by typing =TURNED(yourrange) where yourrange is say A1:A10. You could bundle this up as an Add-In, too, so it's available on to all Excel files on your machine. Shout out if you want to know how to do this.

    Public Function Turned(MyRng As Range) As String

    For Each c In MyRng
    If c.Font.ColorIndex = 3 And c.Value <> "" Then Red = Red + 1
    If c.Font.ColorIndex = 32 And c.Value <> "" Then blue = blue + 1
    If c.Font.ColorIndex = xlColorIndexAutomatic And c.Value <> "" Then black = black + 1
    Next c

    Turned = Red & " red " & blue & " blue " & black & " black"

    End Function


    Edit- Sorry, Nate, didn't realise you'd already answered this, the above is my first ever function and my beer-sodden brain took a while to get around it

    [ This Message was edited by: Mudface on 2002-02-23 14:12 ]

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I suppose it would be more helpful if I answered the question, sorry about that. Same deal, put the following in a normal module:

    Function CountByColor(InputRange As Range, ColorRange As Range) As Double
    Dim cl As Range, TempCount As Double, ColorIndex As Integer
    ColorIndex = ColorRange.Cells(1, 1).Font.ColorIndex
    TempCount = 0
    On Error Resume Next
    For Each cl In InputRange.Cells
    If cl.Font.ColorIndex = ColorIndex Then TempCount = TempCount + 1
    Next cl
    On Error GoTo 0
    Set cl = Nothing
    CountByColor = TempCount
    End Function

    Now use the following formula:

    =CountByColor(A3:A9,A1)

    Where the first paramter is the range to count and cell a1 has the font criteria to evaluate against (this cell must have a value).

    HTH. Cheers, Nate

  9. #9
    New Member
    Join Date
    Feb 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    THANKS NATE!

    I have added one little piece to the code to disregard empty cells. I have one more question that you might be able to help me with.

    The formula works great the first time you run it, but if a cell text color is changed from red to another color after that, the result does not change. Is there an additional step that I put into my code that checks for changes to the cells prompts it to recount?

    Function CountByColor(InputRange As Range, ColorRange As Range) As Double
    Dim cl As Range, TempCount As Double, ColorIndex As Integer
    ColorIndex = ColorRange.Cells(1, 1).Font.ColorIndex
    TempCount = 0
    On Error Resume Next
    For Each cl In InputRange.Cells
    If cl.Value <> "" And cl.Font.ColorIndex = ColorIndex Then TempCount = TempCount + 1
    Next cl
    On Error GoTo 0
    Set cl = Nothing
    CountByColor = TempCount
    End Function

    Thanks again,

    Jim

  10. #10
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    I'm having some trouble with this as well. This formula's not too keen with even a manual re-calc.

    I wonder if there's an event procedure you would do that would rewrite the formula to the appropriate cell, like workbook_close or sheetdeactive or selectionchange where:

    range(YourRange) = "=CountByColor(A3:A9,A1)"

    or call the function in the sub and return the value:

    range("a40") = CountByColor(range("a3:9"),range("a1"))

    Could do a workbook_open and ontime procedure combination to have the result refresh every few seconds....

    Not perfect....

    Cheers, Nate

    [ This Message was edited by: NateO on 2002-02-25 08:44 ]

User Tag List

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