Oct 12 2017 02:29 PM
Oct 12 2017 02:29 PM
I installed few add-ins for counting "Conditional formatted" cells but all are generating an instant (one-time) numeric result by activating that command.
Also, I tried a code which gives an instant counting of 1 picked color without a total sum of all other colors -in my case 12 colors in a matrix. ("How to use the code to count colored cells and sum their values" - https://www.ablebits.com/office-addins-blog/2013/12/12/count-sum-by-color-excel/ )
May 20 2019 03:01 AM
Hi Jamil,
Thanks for sharing the code for counting colored cells in a conditionally formatted sheet. Just a quick question, "Can you please help me alter the code to make it count the colored cells in a specific row. This current code is default for range starting from cell A3. Say suppose I want to count the colored cells for range A4 to G4 in your sheet 'COUNTBYCOLOR.xls, it does not give me correct result."
Thanks
May 20 2019 05:16 PM
It is because your CF is set C3:G3 and not C3:C5
so only C3:G3 works because your CF has this range.
May 20 2019 05:18 PM
if you want to count by row then your conditional formatting applied range should be also by that row and now the entire range. so if you want it to work on A4 to G4 then set up the conditional formatting range to be applied only on A4:G4
Jun 09 2019 11:07 PM
Hi There
I know very little about VBA's.
I've got a very large s/sheet with data that I've just inherited. I need to add this months data to the master sheet (hystoric) and I'm trying to make the process easier and less time consuming (previously done manually). It's got columns with Customer Name, Address, Territory, Serial Number, Device Name, etc (columns A to O) and then approx 6900 rows of data. In my Serial Number column I've got a conditional format (column H) (format values where this formula is true =countif(if serial number is different to last month) - fill - red) and then I've got a conditional format in column K (format values where device name is different to last month - fill - blue). I've also got subtotals - count per territory number.
What I'm trying to achieve is to count all the non-coloured Serial Numbers (cells in H) and all the change in device name (non-coloured cells in K (less non-coloured cells in H)).
I've inserted your UDF in VBA. In H373 (first change in territory) I've typed =COUNTConditionColorCells and hit Enter and get the "#Value!" error. Not sure what I'm doing wrong, please help.
Jun 11 2019 02:00 PM
I'm trying this formula and I keep getting 0's. I'm not sure what I'm doing wrong and I'm wondering if you can help. See my data.
Jun 19 2019 04:10 AM
Jun 19 2019 04:18 AM
If you read my earlier posts, you will see that I mentioned UDF only works if CF is set using formula not build it conditions.
I changed the CF in the attached file as an example and see it works.
Jun 27 2019 07:56 AM
Hi Jamil, trying to use the count feature on conditional formatted cells, but the count is not accurate. My range of cells are restricted to 1 row at a time of cell highlighted in blue, but it appears to give the same result, regardless of the range provided. The spreadsheet has been attached. Any thought?
Jul 01 2019 12:06 PM
Hi @Jamil Mohammad ,
I have a similar question, however, when I applied the codes it only counts the cell by column, which I also need it to be counted by row. I wonder if it is because each column has a different condition formatting criteria. Could you please help? Please see the attached file.
Thanks!!
Jul 01 2019 01:15 PM
@Deleted
Hi,
Your CF range was vertical while the formula range was horizontal. so i changed your CF range to horizontal and it works now. see attached.
Jul 01 2019 01:17 PM
Hi, please read my earlier posts. the UDF do not work if the CF is set using built-in conditions. UDF works only when you set conditions with formula. "Use a formula to determine which cell to format"
if you set your CF using formula, then it works.
Jul 07 2019 03:26 PM
Hi could you please check my file, i can't make it work, it gives me the error:
#VALOR!
English is not my first language, so i tried to make it work reading the posts here. Thanks if you can take a look into my file.
Jul 17 2019 11:44 AM - edited Jul 17 2019 11:47 AM
Hi, I try your code and i still cant count color in my workbook. keep saying no colour, eventough its already the same color
can you take a look at my file? really appreciate it
is it because I am using excel 2013?
Jul 18 2019 11:17 AM
Jul 19 2019 09:50 AM
Hi Jamil,
I've been using your UDF for counting conditional cells and it has been working great. I was wondering if there is a way to modify the code so that when I filter my data in a table, the totals will update automatically without counting the hidden rows.
Jul 29 2019 10:19 AM
Sep 02 2019 12:23 AM
I have a rather large excel file with 480 columns and 1451 rows. I currently have conditional formatting set up to find - and color - the largest value in each row. What I need to know is the count of row maximums in each column. I see that your solution only works when the count function and conditional formatting are applied to the same range. I already know how many I have per row - 1. Is there any way to count conditional formatting when the ranges don't match?
Sep 04 2019 08:10 AM
Sep 05 2019 02:47 PM
No, I'm trying to count when the condition is met, however, my conditional formatting runs across rows. I need to count instances when conditional formatting is matched within columns. As you had previously mentioned to someone else, your tool for counting conditional formatting only works when the range and the area being counted match. My question is: is there a workaround for when the conditional formattting range and the range being summed do not match?
FYI the same condition is being tested for within each row for each row within the summed columns.
Sep 05 2019 02:51 PM - edited Sep 05 2019 03:20 PM
I know it is possible to sort/filter via color and I could do that - and manually count - for each of my 480 columns; but since my data will change frequently over the course of the project, I'd prefer not to manually count 480 columns dozens, possibly hundreds, of times. I would much prefer to have a readout listed for each column.