Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jan 2002
    Location
    Amarillo, Texas, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count red formatted cells (Excel 2000)

    I'd like to do a count on cells that have been formatted with a red font. I tried a countif but don't know how to reference a cell format. Is this possible with a formula?
    Thanks,
    Jackie Davis

  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 red formatted cells (Excel 2000)

    Try the following User Defined Function :<pre>Function CountRed(Rng As Range) As Long
    Dim oCell As Range
    For Each oCell In Rng
    If oCell.Font.ColorIndex = 3 <font color=blue>And oCell.Value <> ""</font color=blue> Then
    CountRed = CountRed + 1
    End If
    Next
    End Function</pre>

    If you want to include blank cells with red font formatting, remove the part in blue.

    Just place the code in a general module in your workbook and use

    =CountRed(Range)

    Andrew C

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count red formatted cells (Excel 2000)

    Jackie,

    Check out Chips page on Functions For Working With Cell Colors.

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

    Re: Count red formatted cells (Excel 2000)

    Andrew's function is spot on. However, I think that you need to be aware of two things:

    1- This function counts cells where the font is red, not where the background is red. Is that what you want?

    2- This function will not automatically update if the font color in the range is changed without any change in the content of the cell. You will have to manually force an update if that happens.
    Legare Coleman

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Count red formatted cells (Excel 2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Jackie

    All of the responses so far were great, and allow me to add my two cents in:

    Andrew's code misses two cases 1) Conditonal Formating and 2) if the Format is set to something like: <font color=blue> 0.00;Red0.00 </font color=blue> which will turn the negative numbers to <font color=red>red</font color=red>. Note I could not use the [ and the ] with the word Red in them because that would have affected this message. But I hope this would be clear to 99% of the loungers here. The 1% will ask I hope.

    So the revised code should be like this:

    <font color=blue>

    Function CountRed(Rng As Range) As Long
    Dim oCell As Range
    Dim i As Long

    For Each oCell In Rng
    If oCell.Font.ColorIndex = 3 And _
    oCell.Value <> "" And _
    InStr(1, oCell.NumberFormat, "Red") > 0 Then <font color=448800> '/This handles the negative Reds </font color=448800>
    CountRed = CountRed + 1
    End If

    <font color=448800> '/In Case oCell does not have Conditional Formatting you will get an error. </font color=448800>
    On Error Resume Next
    For i = 1 To 3
    If oCell.FormatConditions(i).Font.Color = 255 Then <font color=448800> '/I think 255 is Red, test if first. </font color=448800>
    CountRed = CountRed + 1
    End If
    Next i
    On Error GoTo 0 <font color=448800> '/Stop Error handeling </font color=448800>
    Next
    End Function
    </font color=blue>

    I hope this helps...

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    New Lounger
    Join Date
    Jan 2002
    Location
    Amarillo, Texas, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count red formatted cells (Excel 2000)

    Thank you all so much. Andrew's reply worked great. I did just want to count on the font and I'll manually update if I need to. I've bookmarked Chip's great page for the future.

    Thanks again!
    Jackie

Posting Permissions

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