here is a way.

Put the code in a standard code module, and then count like this

=SUMPRODUCT(--(CFColorindex(A1:A20)=3)

or

=SUMPRODUCT(--(CFColorindex(A1:A20)=CFColorindex(E1))

for pattern, or

=SUMPRODUCT(--(CFColorindex(A1:A20, TRUE)=3)

for text

Option Explicit

'---------------------------------------------------------------------
Function CFColorIndex(ByVal rng As Range, _
Optional text As Boolean = False) As Variant
'---------------------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim aryColours As Variant
Dim iCI As Long

If rng.Areas.Count 1 Then
CFColorIndex = CVErr(xlErrValue)
Exit Function
End If

If rng.Cells.Count = 1 Then

aryColours = CFmet(rng, text)

Else
aryColours = rng.Value
i = 0
For Each row In rng.Rows
i = i + 1
j = 0

For Each cell In row.Cells
j = j + 1

aryColours(i, j) = CFmet(cell, text)

Next cell

Next row

End If

CFColorIndex = aryColours

End Function

'---------------------------------------------------------------------
Private Function CFmet(ByVal rng As Range, _
ByVal text As Boolean) As Long
'---------------------------------------------------------------------
Dim oFC As FormatCondition
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long
Dim iCF As Long

Set rng = rng(1, 1)
If rng.FormatConditions.Count 0 Then

For Each oFC In rng.FormatConditions

CFmet = -1

iCF = iCF + 1
If oFC.Type = xlCellValue Then

Select Case oFC.Operator

Case xlEqual
CFmet = rng.Value = oFC.Formula1
Case xlNotEqual
CFmet = rng.Value < oFC.Formula1
Case xlGreater
CFmet = rng.Value oFC.Formula1
Case xlGreaterEqual
CFmet = rng.Value = oFC.Formula1
Case xlLess
CFmet = rng.Value < oFC.Formula1
Case xlLessEqual
CFmet = rng.Value <= oFC.Formula1
Case xlBetween
CFmet = (rng.Value = oFC.Formula1 And _
rng.Value <= oFC.Formula2)
Case xlNotBetween
CFmet = (rng.Value < oFC.Formula1 Or _
rng.Value oFC.Formula2)
End Select

Else
're-adjust the formula back to the formula that applies
'to the cell as relative formulae adjust to the activecell
With Application
iRow = rng.row
iColumn = rng.Column
sF1 = .Substitute(oFC.Formula1, "ROW()", iRow)
sF1 = .Substitute(sF1, "COLUMN()", iColumn)
sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng)
End With

If Not IsError(rng.Parent.Evaluate(sF1)) Then
If rng.Parent.Evaluate(sF1) Then
If text Then
CFmet = oFC.Font.ColorIndex
Else
CFmet = oFC.Interior.ColorIndex
End If
Exit For
End If
End If

End If

Next oFC

End If 'rng.FormatConditions.Count 0

End Function



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Chad" wrote in message
...
Alan,

It is a bit more complicated sinces each cells conditional formatting is
based off of completely different cells. For instance, one cell has two
conditional formats of:

Condition 1: =IF($F$91=$B$91,IF($C$91="W",TRUE,FALSE))
Condition 2: =IF($F$91=$B$92,IF($C$92="W",TRUE,FALSE))

The cell below this one has the following formats:

Condition 1: =IF($F$93=$B$93,IF($C$93="W",TRUE,FALSE))
Condition 2: =IF($F$93=$B$94,IF($C$94="W",TRUE,FALSE))

Also, since I am going to be copying this entire column more than 50
times,
I don't want to have to rework all the cells in the formulas. In my
opinion,
the best solution would be:

=COUNTIF(F11:F94,[Pattern or Text = X])

Is there a variable name for the patern or text format for a cell?