Counting the number of rows based on the color

[Gan]

Hi,

Using Conditional Formatting, I color the cells in a column based on its
value. For Example : If the value is between 0 and 3 then I color the cell as
Green. If the value is between 3 and 4.5 then I color the cell as Yellow. If
the value is greater than 4.5 then I color the cell as Red. This was achieved
using Conditional Formatting. But now I want to count the number of Green,
Red and Yellow cells. For this I created a function to count the cells.

Function ColorFunction(rColor As Range, rRange As Range) As Long
Dim lCol As Long
Dim lActCol As Long
Dim lCount As Long
Dim rCell As Range
Application.Volatile True

lCol = rColor.Interior.ColorIndex

For Each rCell In rRange
lActCol = rCell.Interior.ColorIndex

If lCol = lActCol And rCell.Text <> "" Then
lCount = lCount + 1
End If
Next
ColorFunction = lCount
End Function

But some how with conditional formatting the function is not working as the
Interior.ColorIndex is not matching with the rColor cell's ColorIndex.

Is there anyway that I can do to get the count based on the color rather
than doing it manually? If I color the cells manually without conditional
formatting then the function works fine without any problem, but when I
format the cells using conditional formatting it doesn't work.

Thanks,
Gan

 

[Jacob Skaria]

You cannot get that using Interior.ColorIndex...Need a bit more coding...Copy
paste the below UDF to a module and try the below formula...

=GetCFColorCount(G5,J5:J18)

Function GetCFColorCount(rColor As Range, rRange As Range) As Long
Dim rcell As Range
Application.Volatile True
For Each rcell In rRange
If rColor.Interior.ColorIndex = CLng(GetCFColorIndex(rcell)) Then
GetCFColorCount = GetCFColorCount + 1
End If
Next
End Function

Function GetCFColorIndex(C As Range) As Variant
'Jacob Skaria
Dim intCount As Integer, FC As FormatCondition, blnMatch As Boolean
If C.Count <> 1 Then Exit Function

For intCount = 1 To C.FormatConditions.Count
'Loop through each Contidional Formatting
Set FC = C.FormatConditions(intCount)

If FC.Type = 1 Then
'Handle Type1-xlExpression (If 'Cell Value Is')
Select Case FC.Operator
Case xlBetween '1
If C.Value >= GetCFV(FC.Formula1) And C.Value _
<= GetCFV(FC.Formula2) Then blnMatch = True: Exit For
Case xlNotBetween '2
If C.Value < GetCFV(FC.Formula1) Or C.Value _

GetCFV(FC.Formula2) Then blnMatch = True: Exit For

Case xlEqual '3
If C.Value = GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlNotEqual '4
If C.Value <> GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlGreater '5
If C.Value > GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlGreaterEqual '6
If C.Value >= GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlLess '7
If C.Value < GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlLessEqual '8
If C.Value <= GetCFV(FC.Formula1) Then blnMatch = True: Exit For
End Select
Else
'Handle Type2-xlExternal (If 'Formula Is')
If Evaluate(Application.ConvertFormula( _
Application.ConvertFormula(FC.Formula1, xlA1, xlR1C1), _
xlR1C1, xlA1, , C)) Then blnMatch = True: Exit For
End If
Next

If blnMatch Then GetCFColorIndex = FC.Interior.ColorIndex
End Function

Function GetCFV(strData As Variant)
'Get text string or numeric from CF formula
If Not IsNumeric(strData) Then
GetCFV = Mid(strData, 3, Len(strData) - 3)
Else
GetCFV = CDbl(strData)
End If
End Function

If this post helps click Yes

 

[T. Valko]

Why reinvent the wheel?

You can use simple COUNTIFs (or SUMPRODUCT) to do this.