Counting cells with green text.

[jerrykel]

Good morning,
I am trying to do something simple and failing miserably...

I have a spreadsheet with manually entered data. The green text is manually entered as well. I need to count the number of cells with green text in any given row of data and put the sum in column C. The data starts in column E. The data set will grow over time.
I am trying to do this with formulas. Can't seem to make countif or sumif work. If VBA is required then that is fine to.

Thank you for your assistance,

 

[Trevor G]

This will place a total in C2 for cells coloured green, if you want the font then change interior to font. The second sub is filling a range so you dont need it, but i've used so added it here. If im not reading your question correctly please show some data samples

Sub countcol()
Dim countGreen As Integer

Dim cell As Range
For Each cell In Range("a1:a20")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C2") = countGreen
MsgBox countGreen

End Sub
Sub col()
Dim ran As Range
For Each ran In Range("A1:a34")
ran.Interior.Color = vbGreen
Next ran

End Sub

 

[jerrykel]

Thanks you TreverG for the quick reply.
It is still a work in progress.
Guess I should of stated this better. Data starts in Column E6 and goes on to about CZ31. Most cells are in black text. There are a few in green text. I need to count the number of green text cells in each row and put the sum in column C on rows 6 - 31.

 

[Kenneth Hobson]

From http://www.cpearson.com/excel/colors.aspx

Function CountColor(InRange As Range, ColorIndex As Long, _
    Optional OfText As Boolean = False) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' CountColor
' This function counts the cells in InRange whose ColorIndex
' is equal to the ColorIndex parameter. The ColorIndex of the
' Font is tested if OfText is True, or the Interior property
' if OfText is omitted or False. If ColorIndex is not a valid
' ColorIndex (1 -> 56, xlColorIndexNone, xlColorIndexAutomatic)
' 0 is returned.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim r As Range
Dim N As Long

Application.Volatile True
If IsValidColorIndex(ColorIndex) = False Then
    CountColor = 0
    Exit Function
End If

For Each r In InRange.Cells
    If OfText = True Then
        If r.Font.ColorIndex = ColorIndex Then
            N = N + 1
        End If
    Else
        If r.Interior.ColorIndex = ColorIndex Then
            N = N + 1
        End If
    End If
Next r

CountColor = N

End Function

 

[jerrykel]

Thank you for the reply Kenneth.
I changed the criteria to look for green filled cells and the vba code below works. I am interested in minimizing the number of lines in the code. Any help in this endeavor would be most appreciated.

Sub greencount()
Dim countGreen As Integer

Dim cell As Range

For Each cell In Range("e6:cd6")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C6") = countGreen
countGreen = 0

For Each cell In Range("e7:cd7")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C7") = countGreen
countGreen = 0

For Each cell In Range("e8:cd8")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C8") = countGreen
countGreen = 0

For Each cell In Range("e9:cd9")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C9") = countGreen
countGreen = 0

For Each cell In Range("e10:cd10")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C10") = countGreen
countGreen = 0

For Each cell In Range("e11:cd11")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C11") = countGreen
countGreen = 0

For Each cell In Range("e12:cd12")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C12") = countGreen
countGreen = 0

For Each cell In Range("e13:cd13")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C13") = countGreen
countGreen = 0

For Each cell In Range("e14:cd14")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C14") = countGreen
countGreen = 0

For Each cell In Range("e15:cd15")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C15") = countGreen
countGreen = 0

For Each cell In Range("e16:cd16")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C16") = countGreen
countGreen = 0

For Each cell In Range("e17:cd17")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C17") = countGreen
countGreen = 0

For Each cell In Range("e18:cd18")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C18") = countGreen
countGreen = 0

For Each cell In Range("e19:cd19")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C19") = countGreen
countGreen = 0

For Each cell In Range("e20:cd20")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C20") = countGreen
countGreen = 0

For Each cell In Range("e21:cd21")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C21") = countGreen
countGreen = 0

For Each cell In Range("e22:cd22")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C22") = countGreen
countGreen = 0

For Each cell In Range("e23:cd23")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C23") = countGreen
countGreen = 0

For Each cell In Range("e24:cd24")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C24") = countGreen
countGreen = 0

For Each cell In Range("e25:cd25")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C25") = countGreen
countGreen = 0

For Each cell In Range("e26:cd26")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C26") = countGreen
countGreen = 0

For Each cell In Range("e27:cd27")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C27") = countGreen
countGreen = 0

For Each cell In Range("e28:cd28")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C28") = countGreen
countGreen = 0

For Each cell In Range("e29:cd29")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C29") = countGreen
countGreen = 0

For Each cell In Range("e30:cd30")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C30") = countGreen
countGreen = 0

For Each cell In Range("e31:cd31")
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C31") = countGreen
countGreen = 0

End Sub

 

[Kenneth Hobson]

Please use code tags for code. The # icon adds them for you and then paste between them.

vbGreen has a value of 65280. The index value is 4. You have to use the index values and not the color values when using index functions. I prefer color values though as palettes can change.

In the code from Chip's site, it also needed IsValidColorIndex(). I normally just add Chip's color module. I listed that routine below but you won't need it for my solution. You will notice that I added a formula method as well. Normally, one would want the formula so that it updates when a calculation occurs. You could just copy the formula into a Module without using anything other than CountRGBColors().

If you don't know what a color value is, type this into the VBE Immediate window and press Enter:
?vbGreen

Sub CountVBGreen()
  Dim cell As Range
  For Each cell In Range("C6:C31")
    cell.Value = CountRGBColors(Range("E" & cell.Row & ":CD" & cell.Row), vbGreen)
    'cell.Formula = "=CountRGBColors(E" & cell.Row & ":CD" & cell.Row & ", " & vbGreen & ")"
  Next cell
End Sub

Function CountRGBColors(InRange As Range, Color As Long, Optional OfText As Boolean = False) As Long
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  ' CountColor
  ' This function counts the cells in InRange whose Color
  ' is equal to the Color parameter. The Color of the
  ' Font is tested if OfText is True, or the Interior property
  ' if OfText is omitted or False. If Color is not a valid
  ' Color (1 -> 56, xlColorIndexNone, xlColorIndexAutomatic)
  ' 0 is returned.
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  
  Dim r As Range
  Dim N As Long
   
  For Each r In InRange.Cells
      If OfText = True Then
          If r.Font.Color = Color Then
              N = N + 1
          End If
      Else
          If r.Interior.Color = Color Then
              N = N + 1
          End If
      End If
  Next r
  
  CountRGBColors = N
End Function

Private Function IsValidColorIndex(ColorIndex As Long) As Boolean
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  ' IsValidColorIndex
  ' This returns TRUE if ColorIndex is between 1 and 56 or equal
  ' to either xlColorIndexNone or xlColorIndexAutomatic. It
  ' returns FALSE otherwise.
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  Select Case ColorIndex
      Case 1 To 56, xlColorIndexNone, xlColorIndexAutomatic
          IsValidColorIndex = True
      Case Else
          IsValidColorIndex = False
  End Select
End Function

 

[jerrykel]

Thanks for the help.
Your code example works perfectly and is considerably more compact then the one I was using.

 

[Alpacino]

Sub greencount()
Dim countGreen As Integer
Dim cell As Range
Dim r as integer

For r = 6 To 35
For Each cell In Range("e" & r,"cd" & r)
If cell.Interior.Color = vbGreen Then countGreen = countGreen + 1
Next cell
Range("C" & r) = countGreen
countGreen = 0
Next r

End Sub

 

[jerrykel]

Thanks to Trever, Kenneth , and Alpacino.
But Kenneth's and Al's do the job perfectly. Nice to have multiple options.