• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

count conditional formatted cells

X

xljgd

Member
Hello All,


I have been checking all the posts and the VBA code and also used

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


codes from the above. But i dont think i am getting the desired results.


Also since the code does not recalculate. how to call it from a button control to be able to initialize the code and get the result when the cells change colors.


In my work sheet formula i should be getting the result as 2 also i dont understand the significance of =CountOfCF(A5:A9,1) having 1,2,3 or -1 . I want to count the cells that are colored because of CF within a range.


https://docs.google.com/open?id=0BwD23ALrIXLYNGNFYW90a3ZiN00


Thanks for your response and guidance.
 
Hi ,


Can you check out this file ?


https://docs.google.com/open?id=0B0KMpuzr3MTVaUZkdmJzRjl5MnM


This is courtesy the following :


http://www.vbaexpress.com/kb/getarticle.php?kb_id=190


Narayan
 
Thanks Narayank and Dinesh. Your excel sheet always displays 0 when the button is pressed. i dont want to know what color the cell is but want to count all the cells were conditional formatting is applied.


I have an excel sheet were i have set conditional formatting to cells that have certificate dates in them. if the certificate date expires today than i flag it in the due for renewal list. some certificate are valid till 2013 and so they will have no color filled in them. i basically want to count the number of orange or expired to the number of not colored or valid dates. how to do this.
 
Hello Narayank,


Sorry i spoke to soon here. yes i did select the range but my count does not tally. see the attached xls sheet.


Also right now you are calling the function via a pop up window alert is it possible to have this as line in the work sheet.


https://docs.google.com/open?id=0BwD23ALrIXLYS1Y1Z1k4TjlDc2c


Also i want count for colored and non colored cells . in my example i have 4 cells that are over todays date and yet the count only gives 3. can you explain.
 
Hi ,


Please check your file.


The date 10/6/2012 ( in A7 ) is colored / highlighted ; however , this is not because of the CF rule ; the cell has been formatted as FILL with that color.


The count of non-colored cells will be the total number of cells less the number of colored cells ; can you make this change in the code or do you want me to post the addition here ?


Narayan
 
Hello Naraynak,


Thanks for the code. i checked it on a new set of data and it worked. i tried to play around with the code to get the numbers for non-colored cell but no success. i am very new to VB and my knowledge is limited to copying a piece of code and testing it. i have tried to read each line of code but right now it is beyond my understanding. i would appreciate if you could add the addition to the code. This code lets the user select the range of cells and then counts the cells were CF is in place. in my worksheet i work with a particular column data that has renewal dates running to almost 1000 lines. is there anyway i can get this code to work as a part of the worksheet and increase or decrease as the CF conditions are met.


once again i have learnt a lot and need to learn more. thanks again.
 
I generally find it easier to replicate the CF rules in a Sumproduct formula to do the counting based on the same rules as the CF
 
Hi ,


I'll post the revised code today , if possible ; since it is Sunday , if you can wait , that would be nice.


Narayan
 
Hello Narayan


happy thanksgiving. please dont rush. have a relaxed day with your family. meanwhile i will also try to work out my solution based on Hui suggestions. do appreciate all your help and inputs and this forum has been a key helping tool to solve all my problems.
 
@Xljgd: As you Hui mentioned, you should come up with a formula that mimics exact conditions set in conditional formatting to count highlighted cells.


For your original example, you are highlighting all dates prior to TODAY(). So, to count number of cells highlighted, you can use below formula:


=COUNTIF($A$5:$A$9, "<"&TODAY())


If you are using it in a large sheet with 1000s of dates (That keep increasing or decreasing), you can just apply the COUNTIF() over a very large range like A5:A2004.
 
Hi ,


Is this OK ?


https://docs.google.com/open?id=0B0KMpuzr3MTVSDg3U01LMTEtcVU

or

https://www.dropbox.com/s/0kp712hmm76z03i/testcolor.xlsm

(Mirror by Hui)


Narayan
 
Hello All,


I am quite honored that all of you have taken the time to help me out. Nayran latest vb reply was bang on target and that is what i wanted. VB is a new avenue and it is difficult for me to understand it but it is quite powerful. i will be trying the solutions listed by Chandoo and Hui and see how i make out. I know this does not have anything to do with the question but i wanted to let chandoo know that i enjoyed being a student of his excel school and am so grateful to have this kind of interactive qa forum open and helpful. Thanks again to all of you.
 
Hi Friends,

It is a long time after which I am returning to this wonderful Chandoo Forum. The stars and planets have moved quite great distances since then. Hope you are all in great shape and I am excited to say Hello to every one here.


The topic of counting cells with a certain CF interests me too. I need a simpler way to count formula based CF cells. The CF i have put has three or four different conditions and results in three or four different formats like color and bold text etc.


Hence I prefer to count based on formatted colors of cells.


Chandoo, can you show me a countif formula that can do that for me?


Thanks and best regards,


Salah
 
Hi Narayan ,


I tried to get the code from the link given by you, but it is not accessible.

"https://docs.google.com/open?id=0B0KMpuzr3MTVSDg3U01LMTEtcVU"


Can u hep me in getting the code .
 
The above link works but if you don't have access to Google Docs I have put a copy here in Dropbox

https://www.dropbox.com/s/0kp712hmm76z03i/testcolor.xlsm
 
You must log in or register to reply here.
Back
Top