Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Count cells by font color (Excell 2002)

    Hi

    I am trying to count the cells with the red numbers, they are red by conditional formatting is this possible please.

    Braddy
    If you are a fool at forty, you will always be a fool

  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: Count cells by font color (Excell 2002)

    You can just use a COUNTIF using the same criteria as used in the conditional formatting :

    =COUNTIF(A1:A10,"<32").

    Andrew C

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Count cells by font color (Excell 2002)

    I second Andrew's suggestion, just use the same criteria that the cond format uses.

    Counting by conditional formatting is not trivial:
    See Chip Pearson's site if you decide to head down that route.

    Steve

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Count cells by font color (Excell 2002)

    Hi Steve

    I tried The Chip Pearson Function it works just fine for what I need to do.

    Many thanks to you and Andrew.

    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count cells by font color (Excell 2002)

    Black is 0. VBA has a symbolic constant vbBlack with value 0; you can use this constant to increase readability of your code.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count cells by font color (Excell 2002)

    You can add

    Application.Volatile

    at the beginning of the function. This will force formulas using the function to be recalculated each time something on the worksheet is recalculated.

    Note: you haven't declared i in the function! <img src=/S/scold.gif border=0 alt=scold width=50 height=15>

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

    Re: Count cells by font color (Excell 2002)

    You're macro will NOT do what the user wants. The user specified that the font was colored by conditional formatting. Conditional formatting does NOT change the font color index. The font color index for the cell remains whatever the color of the font if the condition is not met. Therefore, your macro will miss any cells that are colored red by conditional formatting.
    Legare Coleman

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count cells by font color (Excell 2002)

    Thats 'cos I didn't have Option Explicit at the top of the module. Thanx for that scolding...Nothing like a good rap on the knuckles to sort a person out. (I didn't even <img src=/S/eyeout.gif border=0 alt=eyeout width=15 height=15> that!!!)
    Yes...thats right....Application.Volatile. I knew that...just forgot!!! Thanx
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count cells by font color (Excell 2002)

    Legare,
    Thanks for bringing that to my attention....
    I will edit my post and write a "warning" for the users who may reference it!
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count cells by font color (Excell 2002)

    <P ID="edit" class=small>(Edited by RudiS on 25-Nov-04 08:48. WARNING: As Legare points out in Post 429741, this macro will not function on cells that are formatted by Conditional Formatting. ONLY USE THIS FUNCTION ON CELLS THAT ARE FORMATTED USING STANDARD FORMATTING TECHNIQUES!!!)</P>Here is a macro that can do the job.

    Function CountCol(CountRange As Range)
    Application.Volatile
    Dim i as Integer
    Dim CC As Integer
    For i = 1 To CountRange.Cells.Count
    If CountRange.Cells(i).Font.Color = vbRed Then
    CC = CC + 1
    End If
    Next i
    CountCol = CC
    End Function

    One question:
    How can I find the numeric value of the default (black) font colour, so the function can be generalised to count all colour font. I can then say : If CountRange.Cells(i).Font.Color <> 00001? Then.....

    PS : To have the formula recalculate if changes of colour occur in the range...Press SHIFT + CTRL + ALT + F9 (This forces a global recalculation across excel!)
    Regards,
    Rudi

Posting Permissions

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