Count cells that meetin conditional formatting criteria
 

I have a column of cells that have conditional formatting to change the cell
color & text style if the condition is true.

Is there a way to count the total number of cells in which the conditional
formatting is true?

Thanks in advance.

Count cells that meetin conditional formatting criteria
 

Yes there is, just use the condition you are using in the CF to count them.
Post the condtion(s) you're using,
Regards,
Alan.
"Chad" wrote in message
...
I have a column of cells that have conditional formatting to change the
cell
color & text style if the condition is true.

Is there a way to count the total number of cells in which the conditional
formatting is true?

Thanks in advance.

Count cells that meetin conditional formatting criteria
 

use the same test as you used for the formatting in a =countif ()
--
paul

remove nospam for email addy!



"Chad" wrote:

I have a column of cells that have conditional formatting to change the cell
color & text style if the condition is true.

Is there a way to count the total number of cells in which the conditional
formatting is true?

Thanks in advance.

Count cells that meetin conditional formatting criteria
 

Use a counting formula that makes the same tests as the CF, such as

=SUMPRODUCT(--(A2:A20=TODAY()-7),--(A2:A20<TODAY())



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Chad" wrote in message
...
I have a column of cells that have conditional formatting to change the
cell
color & text style if the condition is true.

Is there a way to count the total number of cells in which the conditional
formatting is true?

Thanks in advance.

Count cells that meetin conditional formatting criteria
 

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?

Count cells that meetin conditional formatting criteria
 

Hi Chad,

Chip Pearson's site is often referred to for your type of problem...

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

Ken Johnson

Count cells that meetin conditional formatting criteria
 

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?