| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bob P - Count conditional formatting
Bob,
You helped me awhile back write some code to count conditional formatting. That worked great!!! For awhile.... Now the client has changed it so that the data with the conditonal formatting is in a pivot table. Everytime the pivot table updates, the result of the CFColorCount function changes to #Value. If we manually change the range to the exact range, the function will work. I created a dynamic range to select the pivot data, but the function won't accept named ranges for the argument. Is there any way the function will allow dynamic ranges??? We really appreciate all of your help. Below is the code you provided us earlier. Public Function CFColorCount(rng As Range, _ ciValue, _ Optional text As Boolean = False) '--------------------------------------------------------------------- Dim cell As Range, row As Range Dim i As Long, j As Long Dim aryColours As Variant If rng.Areas.Count 1 Then CFColorCount = "#Too many areas!" Exit Function End If If rng.Cells.Count = 1 Then CFColorCount = -CLng(CFColorindex(rng, text) = ciValue) Else i = 0 For Each row In rng.Rows i = i + 1 j = 0 For Each cell In row.Cells j = j + 1 CFColorCount = CFColorCount - _ (CLng(CFColorindex(cell, text)) = ciValue) Next cell Next row End If End Function Public Function CFColorindex(rng As Range, _ Optional text As Boolean = False) '--------------------------------------------------------------------- Dim oFC As FormatCondition Dim sF1 As String Dim iRow As Long Dim iColumn As Long Set rng = rng(1, 1) If rng.FormatConditions.Count 0 Then For Each oFC In rng.FormatConditions If oFC.Type = xlCellValue Then Select Case oFC.Operator Case xlEqual CFColorindex = rng.Value = oFC.Formula1 Case xlNotEqual CFColorindex = rng.Value < oFC.Formula1 Case xlGreater CFColorindex = rng.Value oFC.Formula1 Case xlGreaterEqual CFColorindex = rng.Value = oFC.Formula1 Case xlLess CFColorindex = rng.Value < oFC.Formula1 Case xlLessEqual CFColorindex = rng.Value <= oFC.Formula1 Case xlBetween CFColorindex = (rng.Value = oFC.Formula1 And _ rng.Value <= oFC.Formula2) Case xlNotBetween CFColorindex = (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 CFColorindex = rng.Parent.Evaluate(sF1) End If If CFColorindex Then If text Then If Not IsNull(oFC.Font.ColorIndex) Then CFColorindex = oFC.Font.ColorIndex End If Else If Not IsNull(oFC.Interior.ColorIndex) Then CFColorindex = oFC.Interior.ColorIndex End If End If Exit Function End If Next oFC End If 'rng.FormatConditions.Count 0 End Function -- maryj |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Bob P - Count conditional formatting
How are you passing the named range to the function. If you have it in
double quotes, then remove the double quotes. Assume the named range is MyRange, rather than =CFColorCount("MyRange" ,3) if should be =CFColorCount(MyRange ,3) Otherwise, post a sample formula from the worksheet and the definition of the named range (from the refers to textbox in the Insert=Name=Define dialog) -- Regards, Tom Ogilvy "maryj" wrote: Bob, You helped me awhile back write some code to count conditional formatting. That worked great!!! For awhile.... Now the client has changed it so that the data with the conditonal formatting is in a pivot table. Everytime the pivot table updates, the result of the CFColorCount function changes to #Value. If we manually change the range to the exact range, the function will work. I created a dynamic range to select the pivot data, but the function won't accept named ranges for the argument. Is there any way the function will allow dynamic ranges??? We really appreciate all of your help. Below is the code you provided us earlier. Public Function CFColorCount(rng As Range, _ ciValue, _ Optional text As Boolean = False) '--------------------------------------------------------------------- Dim cell As Range, row As Range Dim i As Long, j As Long Dim aryColours As Variant If rng.Areas.Count 1 Then CFColorCount = "#Too many areas!" Exit Function End If If rng.Cells.Count = 1 Then CFColorCount = -CLng(CFColorindex(rng, text) = ciValue) Else i = 0 For Each row In rng.Rows i = i + 1 j = 0 For Each cell In row.Cells j = j + 1 CFColorCount = CFColorCount - _ (CLng(CFColorindex(cell, text)) = ciValue) Next cell Next row End If End Function Public Function CFColorindex(rng As Range, _ Optional text As Boolean = False) '--------------------------------------------------------------------- Dim oFC As FormatCondition Dim sF1 As String Dim iRow As Long Dim iColumn As Long Set rng = rng(1, 1) If rng.FormatConditions.Count 0 Then For Each oFC In rng.FormatConditions If oFC.Type = xlCellValue Then Select Case oFC.Operator Case xlEqual CFColorindex = rng.Value = oFC.Formula1 Case xlNotEqual CFColorindex = rng.Value < oFC.Formula1 Case xlGreater CFColorindex = rng.Value oFC.Formula1 Case xlGreaterEqual CFColorindex = rng.Value = oFC.Formula1 Case xlLess CFColorindex = rng.Value < oFC.Formula1 Case xlLessEqual CFColorindex = rng.Value <= oFC.Formula1 Case xlBetween CFColorindex = (rng.Value = oFC.Formula1 And _ rng.Value <= oFC.Formula2) Case xlNotBetween CFColorindex = (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 CFColorindex = rng.Parent.Evaluate(sF1) End If If CFColorindex Then If text Then If Not IsNull(oFC.Font.ColorIndex) Then CFColorindex = oFC.Font.ColorIndex End If Else If Not IsNull(oFC.Interior.ColorIndex) Then CFColorindex = oFC.Interior.ColorIndex End If End If Exit Function End If Next oFC End If 'rng.FormatConditions.Count 0 End Function -- maryj |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Bob P - Count conditional formatting
Dynamic range:PivotRange=OFFSET('SBT Pivot Data'!$A$9,0,1,COUNTA('SBT Pivot Data'!$A$9:$A$65536),COUNTA('SBT Pivot Data'!$B$8:$IV$8)) Function entered: =CFColorCount(PivotRange,10) Result of Function: #VALUE! When I enter the name of the dynamic range in the name box, it does select the correct range of cells so that is working. I also get the #value error in the function if I try to use a static named range. Thank you!!! -- maryj "Tom Ogilvy" wrote: How are you passing the named range to the function. If you have it in double quotes, then remove the double quotes. Assume the named range is MyRange, rather than =CFColorCount("MyRange" ,3) if should be =CFColorCount(MyRange ,3) Otherwise, post a sample formula from the worksheet and the definition of the named range (from the refers to textbox in the Insert=Name=Define dialog) -- Regards, Tom Ogilvy "maryj" wrote: Bob, You helped me awhile back write some code to count conditional formatting. That worked great!!! For awhile.... Now the client has changed it so that the data with the conditonal formatting is in a pivot table. Everytime the pivot table updates, the result of the CFColorCount function changes to #Value. If we manually change the range to the exact range, the function will work. I created a dynamic range to select the pivot data, but the function won't accept named ranges for the argument. Is there any way the function will allow dynamic ranges??? We really appreciate all of your help. Below is the code you provided us earlier. Public Function CFColorCount(rng As Range, _ ciValue, _ Optional text As Boolean = False) '--------------------------------------------------------------------- Dim cell As Range, row As Range Dim i As Long, j As Long Dim aryColours As Variant If rng.Areas.Count 1 Then CFColorCount = "#Too many areas!" Exit Function End If If rng.Cells.Count = 1 Then CFColorCount = -CLng(CFColorindex(rng, text) = ciValue) Else i = 0 For Each row In rng.Rows i = i + 1 j = 0 For Each cell In row.Cells j = j + 1 CFColorCount = CFColorCount - _ (CLng(CFColorindex(cell, text)) = ciValue) Next cell Next row End If End Function Public Function CFColorindex(rng As Range, _ Optional text As Boolean = False) '--------------------------------------------------------------------- Dim oFC As FormatCondition Dim sF1 As String Dim iRow As Long Dim iColumn As Long Set rng = rng(1, 1) If rng.FormatConditions.Count 0 Then For Each oFC In rng.FormatConditions If oFC.Type = xlCellValue Then Select Case oFC.Operator Case xlEqual CFColorindex = rng.Value = oFC.Formula1 Case xlNotEqual CFColorindex = rng.Value < oFC.Formula1 Case xlGreater CFColorindex = rng.Value oFC.Formula1 Case xlGreaterEqual CFColorindex = rng.Value = oFC.Formula1 Case xlLess CFColorindex = rng.Value < oFC.Formula1 Case xlLessEqual CFColorindex = rng.Value <= oFC.Formula1 Case xlBetween CFColorindex = (rng.Value = oFC.Formula1 And _ rng.Value <= oFC.Formula2) Case xlNotBetween CFColorindex = (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 CFColorindex = rng.Parent.Evaluate(sF1) End If If CFColorindex Then If text Then If Not IsNull(oFC.Font.ColorIndex) Then CFColorindex = oFC.Font.ColorIndex End If Else If Not IsNull(oFC.Interior.ColorIndex) Then CFColorindex = oFC.Interior.ColorIndex End If End If Exit Function End If Next oFC End If 'rng.FormatConditions.Count 0 End Function -- maryj |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Bob P - Count conditional formatting
It works fine Mary. I think the problem is that your pivot table is not
conditionally formatted, which is what it is testing for. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... Dynamic range:PivotRange=OFFSET('SBT Pivot Data'!$A$9,0,1,COUNTA('SBT Pivot Data'!$A$9:$A$65536),COUNTA('SBT Pivot Data'!$B$8:$IV$8)) Function entered: =CFColorCount(PivotRange,10) Result of Function: #VALUE! When I enter the name of the dynamic range in the name box, it does select the correct range of cells so that is working. I also get the #value error in the function if I try to use a static named range. Thank you!!! -- maryj "Tom Ogilvy" wrote: How are you passing the named range to the function. If you have it in double quotes, then remove the double quotes. Assume the named range is MyRange, rather than =CFColorCount("MyRange" ,3) if should be =CFColorCount(MyRange ,3) Otherwise, post a sample formula from the worksheet and the definition of the named range (from the refers to textbox in the Insert=Name=Define dialog) -- Regards, Tom Ogilvy "maryj" wrote: Bob, You helped me awhile back write some code to count conditional formatting. That worked great!!! For awhile.... Now the client has changed it so that the data with the conditonal formatting is in a pivot table. Everytime the pivot table updates, the result of the CFColorCount function changes to #Value. If we manually change the range to the exact range, the function will work. I created a dynamic range to select the pivot data, but the function won't accept named ranges for the argument. Is there any way the function will allow dynamic ranges??? We really appreciate all of your help. Below is the code you provided us earlier. Public Function CFColorCount(rng As Range, _ ciValue, _ Optional text As Boolean = False) '--------------------------------------------------------------------- Dim cell As Range, row As Range Dim i As Long, j As Long Dim aryColours As Variant If rng.Areas.Count 1 Then CFColorCount = "#Too many areas!" Exit Function End If If rng.Cells.Count = 1 Then CFColorCount = -CLng(CFColorindex(rng, text) = ciValue) Else i = 0 For Each row In rng.Rows i = i + 1 j = 0 For Each cell In row.Cells j = j + 1 CFColorCount = CFColorCount - _ (CLng(CFColorindex(cell, text)) = ciValue) Next cell Next row End If End Function Public Function CFColorindex(rng As Range, _ Optional text As Boolean = False) '--------------------------------------------------------------------- Dim oFC As FormatCondition Dim sF1 As String Dim iRow As Long Dim iColumn As Long Set rng = rng(1, 1) If rng.FormatConditions.Count 0 Then For Each oFC In rng.FormatConditions If oFC.Type = xlCellValue Then Select Case oFC.Operator Case xlEqual CFColorindex = rng.Value = oFC.Formula1 Case xlNotEqual CFColorindex = rng.Value < oFC.Formula1 Case xlGreater CFColorindex = rng.Value oFC.Formula1 Case xlGreaterEqual CFColorindex = rng.Value = oFC.Formula1 Case xlLess CFColorindex = rng.Value < oFC.Formula1 Case xlLessEqual CFColorindex = rng.Value <= oFC.Formula1 Case xlBetween CFColorindex = (rng.Value = oFC.Formula1 And _ rng.Value <= oFC.Formula2) Case xlNotBetween CFColorindex = (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 CFColorindex = rng.Parent.Evaluate(sF1) End If If CFColorindex Then If text Then If Not IsNull(oFC.Font.ColorIndex) Then CFColorindex = oFC.Font.ColorIndex End If Else If Not IsNull(oFC.Interior.ColorIndex) Then CFColorindex = oFC.Interior.ColorIndex End If End If Exit Function End If Next oFC End If 'rng.FormatConditions.Count 0 End Function -- maryj |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Bob P - Count conditional formatting
Bob,
The cells in the ptable are using the conditional formatting. The function will sometimes work but then when the ptable is refreshed and the ptable is now a different number of rows/columns, one of 2 things happens - either the values don't update to the correct number and then eventually it changes to the #Value. -- maryj "Bob Phillips" wrote: It works fine Mary. I think the problem is that your pivot table is not conditionally formatted, which is what it is testing for. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... Dynamic range:PivotRange=OFFSET('SBT Pivot Data'!$A$9,0,1,COUNTA('SBT Pivot Data'!$A$9:$A$65536),COUNTA('SBT Pivot Data'!$B$8:$IV$8)) Function entered: =CFColorCount(PivotRange,10) Result of Function: #VALUE! When I enter the name of the dynamic range in the name box, it does select the correct range of cells so that is working. I also get the #value error in the function if I try to use a static named range. Thank you!!! -- maryj "Tom Ogilvy" wrote: How are you passing the named range to the function. If you have it in double quotes, then remove the double quotes. Assume the named range is MyRange, rather than =CFColorCount("MyRange" ,3) if should be =CFColorCount(MyRange ,3) Otherwise, post a sample formula from the worksheet and the definition of the named range (from the refers to textbox in the Insert=Name=Define dialog) -- Regards, Tom Ogilvy "maryj" wrote: Bob, You helped me awhile back write some code to count conditional formatting. That worked great!!! For awhile.... Now the client has changed it so that the data with the conditonal formatting is in a pivot table. Everytime the pivot table updates, the result of the CFColorCount function changes to #Value. If we manually change the range to the exact range, the function will work. I created a dynamic range to select the pivot data, but the function won't accept named ranges for the argument. Is there any way the function will allow dynamic ranges??? We really appreciate all of your help. Below is the code you provided us earlier. Public Function CFColorCount(rng As Range, _ ciValue, _ Optional text As Boolean = False) '--------------------------------------------------------------------- Dim cell As Range, row As Range Dim i As Long, j As Long Dim aryColours As Variant If rng.Areas.Count 1 Then CFColorCount = "#Too many areas!" Exit Function End If If rng.Cells.Count = 1 Then CFColorCount = -CLng(CFColorindex(rng, text) = ciValue) Else i = 0 For Each row In rng.Rows i = i + 1 j = 0 For Each cell In row.Cells j = j + 1 CFColorCount = CFColorCount - _ (CLng(CFColorindex(cell, text)) = ciValue) Next cell Next row End If End Function Public Function CFColorindex(rng As Range, _ Optional text As Boolean = False) '--------------------------------------------------------------------- Dim oFC As FormatCondition Dim sF1 As String Dim iRow As Long Dim iColumn As Long Set rng = rng(1, 1) If rng.FormatConditions.Count 0 Then For Each oFC In rng.FormatConditions If oFC.Type = xlCellValue Then Select Case oFC.Operator Case xlEqual CFColorindex = rng.Value = oFC.Formula1 Case xlNotEqual CFColorindex = rng.Value < oFC.Formula1 Case xlGreater CFColorindex = rng.Value oFC.Formula1 Case xlGreaterEqual CFColorindex = rng.Value = oFC.Formula1 Case xlLess CFColorindex = rng.Value < oFC.Formula1 Case xlLessEqual CFColorindex = rng.Value <= oFC.Formula1 Case xlBetween CFColorindex = (rng.Value = oFC.Formula1 And _ rng.Value <= oFC.Formula2) Case xlNotBetween CFColorindex = (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 CFColorindex = rng.Parent.Evaluate(sF1) End If If CFColorindex Then If text Then If Not IsNull(oFC.Font.ColorIndex) Then CFColorindex = oFC.Font.ColorIndex End If Else If Not IsNull(oFC.Interior.ColorIndex) Then CFColorindex = oFC.Interior.ColorIndex End If End If Exit Function End If Next oFC End If 'rng.FormatConditions.Count 0 End Function -- maryj |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Bob P - Count conditional formatting
Bob,
I hate to ask this, but could I send this new version of the file for you to look at to see if you can figure out what is wrong?? We have been bumping into a brick wall with this for the last 2 weeks. We really appreciate your help!! -- maryj "Bob Phillips" wrote: It certainly won't update, as a the refresh doesn't create any event that the colour calculator picks up on. You could add VBA to refresh the pivot, and call the a sheet calculate within that, force a recalculation. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... Bob, The cells in the ptable are using the conditional formatting. The function will sometimes work but then when the ptable is refreshed and the ptable is now a different number of rows/columns, one of 2 things happens - either the values don't update to the correct number and then eventually it changes to the #Value. -- maryj "Bob Phillips" wrote: It works fine Mary. I think the problem is that your pivot table is not conditionally formatted, which is what it is testing for. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... Dynamic range:PivotRange=OFFSET('SBT Pivot Data'!$A$9,0,1,COUNTA('SBT Pivot Data'!$A$9:$A$65536),COUNTA('SBT Pivot Data'!$B$8:$IV$8)) Function entered: =CFColorCount(PivotRange,10) Result of Function: #VALUE! When I enter the name of the dynamic range in the name box, it does select the correct range of cells so that is working. I also get the #value error in the function if I try to use a static named range. Thank you!!! -- maryj "Tom Ogilvy" wrote: How are you passing the named range to the function. If you have it in double quotes, then remove the double quotes. Assume the named range is MyRange, rather than =CFColorCount("MyRange" ,3) if should be =CFColorCount(MyRange ,3) Otherwise, post a sample formula from the worksheet and the definition of the named range (from the refers to textbox in the Insert=Name=Define dialog) -- Regards, Tom Ogilvy "maryj" wrote: Bob, You helped me awhile back write some code to count conditional formatting. That worked great!!! For awhile.... Now the client has changed it so that the data with the conditonal formatting is in a pivot table. Everytime the pivot table updates, the result of the CFColorCount function changes to #Value. If we manually change the range to the exact range, the function will work. I created a dynamic range to select the pivot data, but the function won't accept named ranges for the argument. Is there any way the function will allow dynamic ranges??? We really appreciate all of your help. Below is the code you provided us earlier. Public Function CFColorCount(rng As Range, _ ciValue, _ Optional text As Boolean = False) '--------------------------------------------------------------------- Dim cell As Range, row As Range Dim i As Long, j As Long Dim aryColours As Variant If rng.Areas.Count 1 Then CFColorCount = "#Too many areas!" Exit Function End If If rng.Cells.Count = 1 Then CFColorCount = -CLng(CFColorindex(rng, text) = ciValue) Else i = 0 For Each row In rng.Rows i = i + 1 j = 0 For Each cell In row.Cells j = j + 1 CFColorCount = CFColorCount - _ (CLng(CFColorindex(cell, text)) = ciValue) Next cell Next row End If End Function Public Function CFColorindex(rng As Range, _ Optional text As Boolean = False) '--------------------------------------------------------------------- Dim oFC As FormatCondition Dim sF1 As String Dim iRow As Long Dim iColumn As Long Set rng = rng(1, 1) If rng.FormatConditions.Count 0 Then For Each oFC In rng.FormatConditions If oFC.Type = xlCellValue Then Select Case oFC.Operator Case xlEqual CFColorindex = rng.Value = oFC.Formula1 Case xlNotEqual CFColorindex = rng.Value < oFC.Formula1 Case xlGreater CFColorindex = rng.Value oFC.Formula1 Case xlGreaterEqual CFColorindex = rng.Value = oFC.Formula1 Case xlLess CFColorindex = rng.Value < oFC.Formula1 Case xlLessEqual CFColorindex = rng.Value <= oFC.Formula1 Case xlBetween CFColorindex = (rng.Value = oFC.Formula1 And _ rng.Value <= oFC.Formula2) Case xlNotBetween CFColorindex = (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 CFColorindex = rng.Parent.Evaluate(sF1) End If If CFColorindex Then If text Then If Not IsNull(oFC.Font.ColorIndex) Then CFColorindex = oFC.Font.ColorIndex End If Else If Not IsNull(oFC.Interior.ColorIndex) Then CFColorindex = oFC.Interior.ColorIndex End If End If Exit Function End If Next oFC End If 'rng.FormatConditions.Count 0 End Function -- maryj |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Bob P - Count conditional formatting
Of course. I am out today, but will be able to take a look tomorrow.
Send it to bob dot ngs at gmail dot com do the obvious -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... Bob, I hate to ask this, but could I send this new version of the file for you to look at to see if you can figure out what is wrong?? We have been bumping into a brick wall with this for the last 2 weeks. We really appreciate your help!! -- maryj "Bob Phillips" wrote: It certainly won't update, as a the refresh doesn't create any event that the colour calculator picks up on. You could add VBA to refresh the pivot, and call the a sheet calculate within that, force a recalculation. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... Bob, The cells in the ptable are using the conditional formatting. The function will sometimes work but then when the ptable is refreshed and the ptable is now a different number of rows/columns, one of 2 things happens - either the values don't update to the correct number and then eventually it changes to the #Value. -- maryj "Bob Phillips" wrote: It works fine Mary. I think the problem is that your pivot table is not conditionally formatted, which is what it is testing for. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... Dynamic range:PivotRange=OFFSET('SBT Pivot Data'!$A$9,0,1,COUNTA('SBT Pivot Data'!$A$9:$A$65536),COUNTA('SBT Pivot Data'!$B$8:$IV$8)) Function entered: =CFColorCount(PivotRange,10) Result of Function: #VALUE! When I enter the name of the dynamic range in the name box, it does select the correct range of cells so that is working. I also get the #value error in the function if I try to use a static named range. Thank you!!! -- maryj "Tom Ogilvy" wrote: How are you passing the named range to the function. If you have it in double quotes, then remove the double quotes. Assume the named range is MyRange, rather than =CFColorCount("MyRange" ,3) if should be =CFColorCount(MyRange ,3) Otherwise, post a sample formula from the worksheet and the definition of the named range (from the refers to textbox in the Insert=Name=Define dialog) -- Regards, Tom Ogilvy "maryj" wrote: Bob, You helped me awhile back write some code to count conditional formatting. That worked great!!! For awhile.... Now the client has changed it so that the data with the conditonal formatting is in a pivot table. Everytime the pivot table updates, the result of the CFColorCount function changes to #Value. If we manually change the range to the exact range, the function will work. I created a dynamic range to select the pivot data, but the function won't accept named ranges for the argument. Is there any way the function will allow dynamic ranges??? We really appreciate all of your help. Below is the code you provided us earlier. Public Function CFColorCount(rng As Range, _ ciValue, _ Optional text As Boolean = False) '--------------------------------------------------------------------- Dim cell As Range, row As Range Dim i As Long, j As Long Dim aryColours As Variant If rng.Areas.Count 1 Then CFColorCount = "#Too many areas!" Exit Function End If If rng.Cells.Count = 1 Then CFColorCount = -CLng(CFColorindex(rng, text) = ciValue) Else i = 0 For Each row In rng.Rows i = i + 1 j = 0 For Each cell In row.Cells j = j + 1 CFColorCount = CFColorCount - _ (CLng(CFColorindex(cell, text)) = ciValue) Next cell Next row End If End Function Public Function CFColorindex(rng As Range, _ Optional text As Boolean = False) '--------------------------------------------------------------------- Dim oFC As FormatCondition Dim sF1 As String Dim iRow As Long Dim iColumn As Long Set rng = rng(1, 1) If rng.FormatConditions.Count 0 Then For Each oFC In rng.FormatConditions If oFC.Type = xlCellValue Then Select Case oFC.Operator Case xlEqual CFColorindex = rng.Value = oFC.Formula1 Case xlNotEqual CFColorindex = rng.Value < oFC.Formula1 Case xlGreater CFColorindex = rng.Value oFC.Formula1 Case xlGreaterEqual CFColorindex = rng.Value = oFC.Formula1 Case xlLess CFColorindex = rng.Value < oFC.Formula1 Case xlLessEqual CFColorindex = rng.Value <= oFC.Formula1 Case xlBetween CFColorindex = (rng.Value = oFC.Formula1 And _ rng.Value <= oFC.Formula2) Case xlNotBetween CFColorindex = (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 CFColorindex = rng.Parent.Evaluate(sF1) End If If CFColorindex Then If text Then If Not IsNull(oFC.Font.ColorIndex) Then CFColorindex = oFC.Font.ColorIndex End If Else If Not IsNull(oFC.Interior.ColorIndex) Then CFColorindex = oFC.Interior.ColorIndex End If End If Exit Function End If Next oFC End If 'rng.FormatConditions.Count 0 End Function -- maryj |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Bob P - Count conditional formatting
Hi Bob,
I was wondering if you have had a chance to look at my problem file? Thank you!! Mary -- maryj "Bob Phillips" wrote: Of course. I am out today, but will be able to take a look tomorrow. Send it to bob dot ngs at gmail dot com do the obvious -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... Bob, I hate to ask this, but could I send this new version of the file for you to look at to see if you can figure out what is wrong?? We have been bumping into a brick wall with this for the last 2 weeks. We really appreciate your help!! -- maryj "Bob Phillips" wrote: It certainly won't update, as a the refresh doesn't create any event that the colour calculator picks up on. You could add VBA to refresh the pivot, and call the a sheet calculate within that, force a recalculation. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... Bob, The cells in the ptable are using the conditional formatting. The function will sometimes work but then when the ptable is refreshed and the ptable is now a different number of rows/columns, one of 2 things happens - either the values don't update to the correct number and then eventually it changes to the #Value. -- maryj "Bob Phillips" wrote: It works fine Mary. I think the problem is that your pivot table is not conditionally formatted, which is what it is testing for. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... Dynamic range:PivotRange=OFFSET('SBT Pivot Data'!$A$9,0,1,COUNTA('SBT Pivot Data'!$A$9:$A$65536),COUNTA('SBT Pivot Data'!$B$8:$IV$8)) Function entered: =CFColorCount(PivotRange,10) Result of Function: #VALUE! When I enter the name of the dynamic range in the name box, it does select the correct range of cells so that is working. I also get the #value error in the function if I try to use a static named range. Thank you!!! -- maryj "Tom Ogilvy" wrote: How are you passing the named range to the function. If you have it in double quotes, then remove the double quotes. Assume the named range is MyRange, rather than =CFColorCount("MyRange" ,3) if should be =CFColorCount(MyRange ,3) Otherwise, post a sample formula from the worksheet and the definition of the named range (from the refers to textbox in the Insert=Name=Define dialog) -- Regards, Tom Ogilvy "maryj" wrote: Bob, You helped me awhile back write some code to count conditional formatting. That worked great!!! For awhile.... Now the client has changed it so that the data with the conditonal formatting is in a pivot table. Everytime the pivot table updates, the result of the CFColorCount function changes to #Value. If we manually change the range to the exact range, the function will work. I created a dynamic range to select the pivot data, but the function won't accept named ranges for the argument. Is there any way the function will allow dynamic ranges??? We really appreciate all of your help. Below is the code you provided us earlier. Public Function CFColorCount(rng As Range, _ ciValue, _ Optional text As Boolean = False) '--------------------------------------------------------------------- Dim cell As Range, row As Range Dim i As Long, j As Long Dim aryColours As Variant If rng.Areas.Count 1 Then CFColorCount = "#Too many areas!" Exit Function End If If rng.Cells.Count = 1 Then CFColorCount = -CLng(CFColorindex(rng, text) = ciValue) Else i = 0 For Each row In rng.Rows i = i + 1 j = 0 For Each cell In row.Cells j = j + 1 CFColorCount = CFColorCount - _ (CLng(CFColorindex(cell, text)) = ciValue) Next cell Next row End If End Function Public Function CFColorindex(rng As Range, _ Optional text As Boolean = False) '--------------------------------------------------------------------- Dim oFC As FormatCondition Dim sF1 As String Dim iRow As Long Dim iColumn As Long Set rng = rng(1, 1) If rng.FormatConditions.Count 0 Then For Each oFC In rng.FormatConditions If oFC.Type = xlCellValue Then Select Case oFC.Operator Case xlEqual CFColorindex = rng.Value = oFC.Formula1 Case xlNotEqual CFColorindex = rng.Value < oFC.Formula1 Case xlGreater CFColorindex = rng.Value oFC.Formula1 Case xlGreaterEqual CFColorindex = rng.Value = oFC.Formula1 Case xlLess CFColorindex = rng.Value < oFC.Formula1 Case xlLessEqual CFColorindex = rng.Value <= oFC.Formula1 Case xlBetween CFColorindex = (rng.Value = oFC.Formula1 And _ rng.Value <= oFC.Formula2) Case xlNotBetween CFColorindex = (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 CFColorindex = rng.Parent.Evaluate(sF1) End If If CFColorindex Then If text Then If Not IsNull(oFC.Font.ColorIndex) Then CFColorindex = oFC.Font.ColorIndex End If Else If Not IsNull(oFC.Interior.ColorIndex) Then CFColorindex = oFC.Interior.ColorIndex End If End If Exit Function End If Next oFC End If 'rng.FormatConditions.Count 0 End Function -- maryj |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNT IF - Conditional Formatting | Excel Discussion (Misc queries) | |||
| Code For Count on Conditional formatting | Excel Programming | |||
| Count conditional formatting | Excel Programming | |||
| Count and Sum with Conditional Formatting Problem | Excel Worksheet Functions | |||
| Count Conditional Formatting | Excel Programming | |||
Linear Mode
