|
Count cells with Conditional Formatting
We found Chip Pearson's site: http://www.cpearson.com/excel/CFColors.htm with
some code that is supposed to count the cells with conditional formatting applied. But we can not get it to work. What are we missing???? We are trying to use this: CountOfCF This function return the number of cells in a range that have a specified conditional format applied. Set the last argument to -1 to look at all format conditions, or a number between 1 and 3 to specify a particular condition. This function requires the ActiveCondition function. You can call this function directly from a worksheet cell with a formula like: =CountOfCF(A1:A10,1) We have that function in a module in the workbook. We also have the code for the Active Condition Function and the GetStripped Function pasted in the same module. In the cell where we want the count to appear, we enter the CountofCf function with the range and the number of the Conditional Formatting condition. We either get a 0, when there should be a number, or we get a circular reference, even though the cell with the formula is not listed in the range. Any ideas what we might be doing wrong? -- maryj |
Count cells with Conditional Formatting
It might be that your CF is formula based, as I recall Chip's code doesn't
cater for that. See here if so http://xldynamic.com/source/xld.CFConditions.html -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... We found Chip Pearson's site: http://www.cpearson.com/excel/CFColors.htm with some code that is supposed to count the cells with conditional formatting applied. But we can not get it to work. What are we missing???? We are trying to use this: CountOfCF This function return the number of cells in a range that have a specified conditional format applied. Set the last argument to -1 to look at all format conditions, or a number between 1 and 3 to specify a particular condition. This function requires the ActiveCondition function. You can call this function directly from a worksheet cell with a formula like: =CountOfCF(A1:A10,1) We have that function in a module in the workbook. We also have the code for the Active Condition Function and the GetStripped Function pasted in the same module. In the cell where we want the count to appear, we enter the CountofCf function with the range and the number of the Conditional Formatting condition. We either get a 0, when there should be a number, or we get a circular reference, even though the cell with the formula is not listed in the range. Any ideas what we might be doing wrong? -- maryj |
Count cells with Conditional Formatting
Can't de-bug your worksheet without seeing the contents. If you just need to
count cells that have conditional formats, try something like: Function cf_cnt(r As Range) As Double cf_cnt = 0 For Each rr In r If rr.FormatConditions.Count 0 Then cf_cnt = cf_cnt + 1 End If Next End Function -- Gary's Student gsnu200704 "maryj" wrote: We found Chip Pearson's site: http://www.cpearson.com/excel/CFColors.htm with some code that is supposed to count the cells with conditional formatting applied. But we can not get it to work. What are we missing???? We are trying to use this: CountOfCF This function return the number of cells in a range that have a specified conditional format applied. Set the last argument to -1 to look at all format conditions, or a number between 1 and 3 to specify a particular condition. This function requires the ActiveCondition function. You can call this function directly from a worksheet cell with a formula like: =CountOfCF(A1:A10,1) We have that function in a module in the workbook. We also have the code for the Active Condition Function and the GetStripped Function pasted in the same module. In the cell where we want the count to appear, we enter the CountofCf function with the range and the number of the Conditional Formatting condition. We either get a 0, when there should be a number, or we get a circular reference, even though the cell with the formula is not listed in the range. Any ideas what we might be doing wrong? -- maryj |
Count cells with Conditional Formatting
Bob,
You are exactly right - the CF is formula based. I'm trying your CFColorCount function but it is returning a "False" value. The CFColorIndex function returns the correct value. Thanks for your help! BTW, I'm not real knowledgeable about code so please be specific with any suggestions to changes. And patient :) Thanks! -- maryj "Bob Phillips" wrote: It might be that your CF is formula based, as I recall Chip's code doesn't cater for that. See here if so http://xldynamic.com/source/xld.CFConditions.html -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... We found Chip Pearson's site: http://www.cpearson.com/excel/CFColors.htm with some code that is supposed to count the cells with conditional formatting applied. But we can not get it to work. What are we missing???? We are trying to use this: CountOfCF This function return the number of cells in a range that have a specified conditional format applied. Set the last argument to -1 to look at all format conditions, or a number between 1 and 3 to specify a particular condition. This function requires the ActiveCondition function. You can call this function directly from a worksheet cell with a formula like: =CountOfCF(A1:A10,1) We have that function in a module in the workbook. We also have the code for the Active Condition Function and the GetStripped Function pasted in the same module. In the cell where we want the count to appear, we enter the CountofCf function with the range and the number of the Conditional Formatting condition. We either get a 0, when there should be a number, or we get a circular reference, even though the cell with the formula is not listed in the range. Any ideas what we might be doing wrong? -- maryj |
Count cells with Conditional Formatting
Are you passing the correct colorindex to the function, and do any cells
meet the CF conditions. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... Bob, You are exactly right - the CF is formula based. I'm trying your CFColorCount function but it is returning a "False" value. The CFColorIndex function returns the correct value. Thanks for your help! BTW, I'm not real knowledgeable about code so please be specific with any suggestions to changes. And patient :) Thanks! -- maryj "Bob Phillips" wrote: It might be that your CF is formula based, as I recall Chip's code doesn't cater for that. See here if so http://xldynamic.com/source/xld.CFConditions.html -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... We found Chip Pearson's site: http://www.cpearson.com/excel/CFColors.htm with some code that is supposed to count the cells with conditional formatting applied. But we can not get it to work. What are we missing???? We are trying to use this: CountOfCF This function return the number of cells in a range that have a specified conditional format applied. Set the last argument to -1 to look at all format conditions, or a number between 1 and 3 to specify a particular condition. This function requires the ActiveCondition function. You can call this function directly from a worksheet cell with a formula like: =CountOfCF(A1:A10,1) We have that function in a module in the workbook. We also have the code for the Active Condition Function and the GetStripped Function pasted in the same module. In the cell where we want the count to appear, we enter the CountofCf function with the range and the number of the Conditional Formatting condition. We either get a 0, when there should be a number, or we get a circular reference, even though the cell with the formula is not listed in the range. Any ideas what we might be doing wrong? -- maryj |
Count cells with Conditional Formatting
Bob,
Yes to both questions. I am looking for cells with a red fill color and the CFColorIndex function does return 3 for a value. There are about 15-20 cells within the range that have a red fill color. -- maryj "Bob Phillips" wrote: Are you passing the correct colorindex to the function, and do any cells meet the CF conditions. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... Bob, You are exactly right - the CF is formula based. I'm trying your CFColorCount function but it is returning a "False" value. The CFColorIndex function returns the correct value. Thanks for your help! BTW, I'm not real knowledgeable about code so please be specific with any suggestions to changes. And patient :) Thanks! -- maryj "Bob Phillips" wrote: It might be that your CF is formula based, as I recall Chip's code doesn't cater for that. See here if so http://xldynamic.com/source/xld.CFConditions.html -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... We found Chip Pearson's site: http://www.cpearson.com/excel/CFColors.htm with some code that is supposed to count the cells with conditional formatting applied. But we can not get it to work. What are we missing???? We are trying to use this: CountOfCF This function return the number of cells in a range that have a specified conditional format applied. Set the last argument to -1 to look at all format conditions, or a number between 1 and 3 to specify a particular condition. This function requires the ActiveCondition function. You can call this function directly from a worksheet cell with a formula like: =CountOfCF(A1:A10,1) We have that function in a module in the workbook. We also have the code for the Active Condition Function and the GetStripped Function pasted in the same module. In the cell where we want the count to appear, we enter the CountofCf function with the range and the number of the Conditional Formatting condition. We either get a 0, when there should be a number, or we get a circular reference, even though the cell with the formula is not listed in the range. Any ideas what we might be doing wrong? -- maryj |
Count cells with Conditional Formatting
Can you send me your workbook, I cannot think of anything obvious?
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... Bob, Yes to both questions. I am looking for cells with a red fill color and the CFColorIndex function does return 3 for a value. There are about 15-20 cells within the range that have a red fill color. -- maryj "Bob Phillips" wrote: Are you passing the correct colorindex to the function, and do any cells meet the CF conditions. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... Bob, You are exactly right - the CF is formula based. I'm trying your CFColorCount function but it is returning a "False" value. The CFColorIndex function returns the correct value. Thanks for your help! BTW, I'm not real knowledgeable about code so please be specific with any suggestions to changes. And patient :) Thanks! -- maryj "Bob Phillips" wrote: It might be that your CF is formula based, as I recall Chip's code doesn't cater for that. See here if so http://xldynamic.com/source/xld.CFConditions.html -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... We found Chip Pearson's site: http://www.cpearson.com/excel/CFColors.htm with some code that is supposed to count the cells with conditional formatting applied. But we can not get it to work. What are we missing???? We are trying to use this: CountOfCF This function return the number of cells in a range that have a specified conditional format applied. Set the last argument to -1 to look at all format conditions, or a number between 1 and 3 to specify a particular condition. This function requires the ActiveCondition function. You can call this function directly from a worksheet cell with a formula like: =CountOfCF(A1:A10,1) We have that function in a module in the workbook. We also have the code for the Active Condition Function and the GetStripped Function pasted in the same module. In the cell where we want the count to appear, we enter the CountofCf function with the range and the number of the Conditional Formatting condition. We either get a 0, when there should be a number, or we get a circular reference, even though the cell with the formula is not listed in the range. Any ideas what we might be doing wrong? -- maryj |
Count cells with Conditional Formatting
Sure! How do I get it to you?
-- maryj "Bob Phillips" wrote: Can you send me your workbook, I cannot think of anything obvious? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... Bob, Yes to both questions. I am looking for cells with a red fill color and the CFColorIndex function does return 3 for a value. There are about 15-20 cells within the range that have a red fill color. -- maryj "Bob Phillips" wrote: Are you passing the correct colorindex to the function, and do any cells meet the CF conditions. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... Bob, You are exactly right - the CF is formula based. I'm trying your CFColorCount function but it is returning a "False" value. The CFColorIndex function returns the correct value. Thanks for your help! BTW, I'm not real knowledgeable about code so please be specific with any suggestions to changes. And patient :) Thanks! -- maryj "Bob Phillips" wrote: It might be that your CF is formula based, as I recall Chip's code doesn't cater for that. See here if so http://xldynamic.com/source/xld.CFConditions.html -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... We found Chip Pearson's site: http://www.cpearson.com/excel/CFColors.htm with some code that is supposed to count the cells with conditional formatting applied. But we can not get it to work. What are we missing???? We are trying to use this: CountOfCF This function return the number of cells in a range that have a specified conditional format applied. Set the last argument to -1 to look at all format conditions, or a number between 1 and 3 to specify a particular condition. This function requires the ActiveCondition function. You can call this function directly from a worksheet cell with a formula like: =CountOfCF(A1:A10,1) We have that function in a module in the workbook. We also have the code for the Active Condition Function and the GetStripped Function pasted in the same module. In the cell where we want the count to appear, we enter the CountofCf function with the range and the number of the Conditional Formatting condition. We either get a 0, when there should be a number, or we get a circular reference, even though the cell with the formula is not listed in the range. Any ideas what we might be doing wrong? -- maryj |
Count cells with Conditional Formatting
I emailed the file to you last Friday. Did you receive it?
-- maryj "Bob Phillips" wrote: my email address in the posting is check my signature to see how to change it. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... Sure! How do I get it to you? -- maryj "Bob Phillips" wrote: Can you send me your workbook, I cannot think of anything obvious? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... Bob, Yes to both questions. I am looking for cells with a red fill color and the CFColorIndex function does return 3 for a value. There are about 15-20 cells within the range that have a red fill color. -- maryj "Bob Phillips" wrote: Are you passing the correct colorindex to the function, and do any cells meet the CF conditions. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... Bob, You are exactly right - the CF is formula based. I'm trying your CFColorCount function but it is returning a "False" value. The CFColorIndex function returns the correct value. Thanks for your help! BTW, I'm not real knowledgeable about code so please be specific with any suggestions to changes. And patient :) Thanks! -- maryj "Bob Phillips" wrote: It might be that your CF is formula based, as I recall Chip's code doesn't cater for that. See here if so http://xldynamic.com/source/xld.CFConditions.html -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... We found Chip Pearson's site: http://www.cpearson.com/excel/CFColors.htm with some code that is supposed to count the cells with conditional formatting applied. But we can not get it to work. What are we missing???? We are trying to use this: CountOfCF This function return the number of cells in a range that have a specified conditional format applied. Set the last argument to -1 to look at all format conditions, or a number between 1 and 3 to specify a particular condition. This function requires the ActiveCondition function. You can call this function directly from a worksheet cell with a formula like: =CountOfCF(A1:A10,1) We have that function in a module in the workbook. We also have the code for the Active Condition Function and the GetStripped Function pasted in the same module. In the cell where we want the count to appear, we enter the CountofCf function with the range and the number of the Conditional Formatting condition. We either get a 0, when there should be a number, or we get a circular reference, even though the cell with the formula is not listed in the range. Any ideas what we might be doing wrong? -- maryj |
Count cells with Conditional Formatting
Yes, I have just been in to the account and seen it. I have responded.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... I emailed the file to you last Friday. Did you receive it? -- maryj "Bob Phillips" wrote: my email address in the posting is check my signature to see how to change it. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... Sure! How do I get it to you? -- maryj "Bob Phillips" wrote: Can you send me your workbook, I cannot think of anything obvious? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... Bob, Yes to both questions. I am looking for cells with a red fill color and the CFColorIndex function does return 3 for a value. There are about 15-20 cells within the range that have a red fill color. -- maryj "Bob Phillips" wrote: Are you passing the correct colorindex to the function, and do any cells meet the CF conditions. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... Bob, You are exactly right - the CF is formula based. I'm trying your CFColorCount function but it is returning a "False" value. The CFColorIndex function returns the correct value. Thanks for your help! BTW, I'm not real knowledgeable about code so please be specific with any suggestions to changes. And patient :) Thanks! -- maryj "Bob Phillips" wrote: It might be that your CF is formula based, as I recall Chip's code doesn't cater for that. See here if so http://xldynamic.com/source/xld.CFConditions.html -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "maryj" wrote in message ... We found Chip Pearson's site: http://www.cpearson.com/excel/CFColors.htm with some code that is supposed to count the cells with conditional formatting applied. But we can not get it to work. What are we missing???? We are trying to use this: CountOfCF This function return the number of cells in a range that have a specified conditional format applied. Set the last argument to -1 to look at all format conditions, or a number between 1 and 3 to specify a particular condition. This function requires the ActiveCondition function. You can call this function directly from a worksheet cell with a formula like: =CountOfCF(A1:A10,1) We have that function in a module in the workbook. We also have the code for the Active Condition Function and the GetStripped Function pasted in the same module. In the cell where we want the count to appear, we enter the CountofCf function with the range and the number of the Conditional Formatting condition. We either get a 0, when there should be a number, or we get a circular reference, even though the cell with the formula is not listed in the range. Any ideas what we might be doing wrong? -- maryj |
| All times are GMT +1. The time now is 07:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com