Prev: Index Match Inconsistencies
Next: Counting Occurrence of a Value within Cells
From: AOdoc on 2 Jun 2010 09:54
I am trying to count the cells in a range that have a certain fill color in
Excel 2003. Is there a way to use COUNTIF? If so, how do I write the criteria
for the color I'm looking for? Or, is there another formula? Many thanks to
whomever can answer this one!
From: Gord Dibben on 2 Jun 2010 10:24
Easiest to Count on the Condition that turned the cells a certain fill
color.

In 2003 you must use VBA code to count colors.

For those colored by CF it involves extra code.

See Chip Pearson's site for code and instructions.

http://www.cpearson.com/excel/CFColors.htm


Gord Dibben MS Excel MVP

On Wed, 2 Jun 2010 06:54:01 -0700, AOdoc <AOdoc(a)discussions.microsoft.com>
wrote:

>I am trying to count the cells in a range that have a certain fill color in
>Excel 2003. Is there a way to use COUNTIF? If so, how do I write the criteria
>for the color I'm looking for? Or, is there another formula? Many thanks to
>whomever can answer this one!

From: Luke M on 2 Jun 2010 10:27
Use the formula that the Conditional format is based on to calculate your
count. There is no built-in way of counting color within XL, and even using
VB, counting conditional formats is tricky.

--
Best Regards,

Luke M
"AOdoc" <AOdoc(a)discussions.microsoft.com> wrote in message
news:8BA424FB-E37C-4724-9369-47F7873B7ED1(a)microsoft.com...
>I am trying to count the cells in a range that have a certain fill color in
> Excel 2003. Is there a way to use COUNTIF? If so, how do I write the
> criteria
> for the color I'm looking for? Or, is there another formula? Many thanks
> to
> whomever can answer this one!


From: Jacob Skaria on 3 Jun 2010 07:36
Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

=GetCFColorSum(E:E,G1)
E:E is the range with CF
G1 is the cell with a similar colorindex as CF (not coloured through CF)

Function GetCFColorSum(varRange As Range, colRange As Range) As Long
Dim cell As Range
For Each cell In Application.Intersect(varRange.Parent.UsedRange, _
varRange)
If GetCFColorIndex(cell) = colRange.Interior.ColorIndex Then _
GetCFColorSum = GetCFColorSum + 1
Next
End Function

Function GetCFColorIndex(c As Range) As Variant
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)
Application.Volatile
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, c) And c.Value _
<= GetCFV(FC.Formula2, c) Then blnMatch = True: Exit For
Case xlNotBetween '2
If c.Value < GetCFV(FC.Formula1, c) Or c.Value _
> GetCFV(FC.Formula2, c) Then blnMatch = True: Exit For
Case xlEqual '3
If c.Value = GetCFV(FC.Formula1, c) Then _
blnMatch = True: Exit For
Case xlNotEqual '4
If c.Value <> GetCFV(FC.Formula1, c) Then _
blnMatch = True: Exit For
Case xlGreater '5
If c.Value > GetCFV(FC.Formula1, c) Then _
blnMatch = True: Exit For
Case xlGreaterEqual '6
If c.Value >= GetCFV(FC.Formula1, c) Then _
blnMatch = True: Exit For
Case xlLess '7
If c.Value < GetCFV(FC.Formula1, c) Then _
blnMatch = True: Exit For
Case xlLessEqual '8
If c.Value <= GetCFV(FC.Formula1, c) 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, c As Range)
'Get text string or numeric from CF formula
If IsNumeric(strData) Then
GetCFV = CDbl(strData)
ElseIf InStr(strData, Chr(34)) Then
GetCFV = Mid(strData, 3, Len(strData) - 3)
Else
GetCFV = Range(Mid(Application.ConvertFormula( _
Application.ConvertFormula(strData, xlA1, xlR1C1), _
xlR1C1, xlA1, , c), 2))
End If
End Function

--
Jacob (MVP - Excel)


"AOdoc" wrote:

> I am trying to count the cells in a range that have a certain fill color in
> Excel 2003. Is there a way to use COUNTIF? If so, how do I write the criteria
> for the color I'm looking for? Or, is there another formula? Many thanks to
> whomever can answer this one!
 | 
Pages: 1
Prev: Index Match Inconsistencies
Next: Counting Occurrence of a Value within Cells