Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting of merged cells, borders don't show correct | Excel Discussion (Misc queries) | |||
Cell Formatting Conditional On Other Cells Fill Color? | Excel Worksheet Functions | |||
Conditional formatting...cont. from 9/25 | Excel Discussion (Misc queries) | |||
how do I maintain my conditional formatting when filtering cells? | Excel Worksheet Functions | |||
conditional formatting on specific text in cells | Excel Discussion (Misc queries) |