+ Reply to Thread
Results 1 to 15 of 15

UDF to Count Conditionally formatted cells by colour

  1. #1
    Registered User
    Join Date
    08-16-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    UDF to Count Conditionally formatted cells by colour

    Hi All,

    New guy here, I have tried searching through the threads but have been unable to find anything that will help me so I apologise if this has been covered before.

    I am using Excel 2010 if that helps.

    I am trying to build a User Defined Function that will count the number of cells which have been formatted a particular colour in a range through conditional formatting.

    I absolutley do need a UDF that counts based on colours, counting by criteria that extablished the CF is not possible as not all cells within the range will have the same conditional formatting rules.

    I have scoured the web for solutions but have not been able to make them work (I am sure this is because of something I am doing).
    I have tried Chip Pearsons example at http://www.cpearson.com/excel/CFColors.htm but alas the code is not working.

    The problem I am having when trying to use Chip's code seems to be somewhere in the 'ActiveCondition' function. I am not getting an error messages in VBA however when I try to use the function in a sheet it returns '#Value!'.

    I have attached a copy of a test workbook that contains the UDF's I have tried to build. I would really appreciate it if anyone could take a look through it and tell me where I am going wrong - particularily with the ActiveCondition function.

    Thanks in advance!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,516

    Re: UDF to Count Conditionally formatted cells by colour

    Pl see attached file with UDF

    CountColor for counting cells with given Colour Index in the given range.

    CountAllColors for counting all cells with Colour Index in the given range.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    08-16-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: UDF to Count Conditionally formatted cells by colour

    i do not see the countallcolors formula? Is this a formula or a condistional formatting?

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,516

    Re: UDF to Count Conditionally formatted cells by colour

    You Pl check once again.
    It is used I3 cell.

  5. #5
    Registered User
    Join Date
    08-16-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: UDF to Count Conditionally formatted cells by colour

    Quote Originally Posted by kvsrinivasamurthy View Post
    Pl see attached file with UDF

    CountColor for counting cells with given Colour Index in the given range.

    CountAllColors for counting all cells with Colour Index in the given range.
    Hi Kvsrinvasamurthy,

    Thank you for taking the time to look at this for me.
    I appreciate it, however it seems to me that the formulas you have used:
    CountColor, and
    CountAllColors.
    only work for cells that have been coloured manually and do not pick up cells that have been coloured using conditional formatting.

    perhaps I am using them incorrectly, any further advice/instruction would be appreciated.

    Ideally, I would like to know why the ActiveCondition UDF in my workboook is not working - I think if this could be solved the colour counting functions developed by Chip Pearson would work fine.

    Thanks Again!
    Last edited by ozzy_q; 08-19-2012 at 10:11 PM. Reason: spelling

  6. #6
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: UDF to Count Conditionally formatted cells by colour

    Is your "macro" enabled??

    btw why not use the formula in your conditional formatting to count the colored cells (coloured using conditional formatting)...
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  7. #7
    Registered User
    Join Date
    08-16-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: UDF to Count Conditionally formatted cells by colour

    Quote Originally Posted by vlady View Post
    Is your "macro" enabled??

    btw why not use the formula in your conditional formatting to count the colored cells (coloured using conditional formatting)...
    Hi Vlady,

    Yes all Macros are enabled.

    As I mentioned in the post at the start of the thread, I cannot use the formula in the conditional formatting to count the cells as the finished table will have many different conditoins in different cells. Therefore there will not be one particular formula that I can count.

  8. #8
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: UDF to Count Conditionally formatted cells by colour

    in Kvsrinvasamurthy formula there are several formulas there... look at his explanations..
    those are the following
    iscf
    IsCFMet1
    ColourIndex
    CountColours
    FormatCount
    CountRed Cells
    Conditional Colour
    ColourCFCell
    Count CFTypes
    IsCFMet
    Cindex
    getstrippedvalue
    active condition
    colorofcf
    count of cf

    did you copied the codes - macro in your file??
    have to save it as "macro enabled workbook"

  9. #9
    Registered User
    Join Date
    08-16-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: UDF to Count Conditionally formatted cells by colour

    those formulas are from my macro enabled workbook that I posted at the start of the thread, in both that workbook, and the one I downloaded containing Kvsrinvasamurthy's changes both files are saved as macro enabled work books (hence the .xlsm file extension).
    Last edited by ozzy_q; 08-19-2012 at 10:10 PM. Reason: spelling

  10. #10
    Registered User
    Join Date
    08-16-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: UDF to Count Conditionally formatted cells by colour

    bump - anyone have any ideas?

  11. #11
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,206

    Re: UDF to Count Conditionally formatted cells by colour

    Rather than counting cells that have been conditionally formatted, count cell that meet the conditions for the formatting.

    If I have CF set to color red any cell containing "stop", it is easier to count cells that contain "stop" rather than to count cells that conditional formatting has turned red.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  12. #12
    Registered User
    Join Date
    08-16-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: UDF to Count Conditionally formatted cells by colour

    Hi Mike,

    Thanks for the suggestion, however unfortunatly it wont work in this instance.
    If you read my earlier post - It is not possible for me to count based on the condition as the columns and rows i want to apply this too will contain different conditions.

    For example, in column a, row 1 might have a condition that will turn it red if the cell = "stop", but row 2 in that same column will have a condition that turns a cell red if the cell is equal to the number 2 or perhaps if it is between 3 and 6 etc, and so on.

    So I really need something that can count based the colour of the conditionally formatted cell.

  13. #13
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,206

    Re: UDF to Count Conditionally formatted cells by colour

    The multiple conditions in different parts of the sheet meerly make for a complicated formula (or UDF).
    Like =SUMPRODUCT(--(A1:Z2="Stop")*(ROW(A1:A2)=1)+(A1:Z2=2)*(ROW(A1:A2)=2))
    or
    =COUNTIF(A1:Z1,"stop)+COUNTIF(A2:Z2,2)

    Why count a bunch of cells that have different conditions? A grand OR?

  14. #14
    Registered User
    Join Date
    08-16-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: UDF to Count Conditionally formatted cells by colour

    Hi Mike,

    I appreciate you taking the time to consider this issue.

    Unfortunatly, the data I am trying to analyse is a table that consists of 150+ unique, lets call them organisations, who have been assesed against 30+ different categories. As such the cells, depending on the column, can be numbers, percentages, True/False. The table mesaures the organisations performance against the category, returning a red, orange or green cell colour. Each of those columns has a differerent set of a conditional formatting rules depending on the category against which the organisation is being assessed. The goal is to be able to tell how many red cells there are in each column and row.

    Yes it is possible to develop a complicated and long counting formula but this is exactly what I am trying to avoid as I will have to do the same sort of analysis against different tables with different rules later on in the process.

    I want to simplify the process and not have to work out the counting formula for each row and column if that is at all possible.

    The Counting conditional formatting colours UDFs that have been suggested by Chip Pearson http://www.cpearson.com/excel/CFColors.htm seem to do what I want however I cant get it to function properly in the workbook I have attached at the start of the post. What I would really like is for someone to help me identify what is wrong with the the three UDFs I copied from Chips website in Module1 of my workbook, namely:
    * GetStrippedValue
    * ActiveCondition
    and
    * CountOfCF

    I believe the issue is in the Active Condition UDF but I cant isolate what it is.

    There is no doubt that in many (most) cases, counting based on a formula rather than conditionally formatting is simpler and quicker but in my case being able to spend the time now and set this up would make the work I am doing on this table and future work on simlar but different tables with different formulas and conditions much much simpler and easier in the long run.

  15. #15
    Registered User
    Join Date
    10-05-2013
    Location
    nottingham
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: UDF to Count Conditionally formatted cells by colour

    Hi did you get an answer to this issue, as I've been looking for one for the past 24hrs with no success.

    Quote Originally Posted by ozzy_q View Post
    Hi Mike,

    I appreciate you taking the time to consider this issue.

    Unfortunatly, the data I am trying to analyse is a table that consists of 150+ unique, lets call them organisations, who have been assesed against 30+ different categories. As such the cells, depending on the column, can be numbers, percentages, True/False. The table mesaures the organisations performance against the category, returning a red, orange or green cell colour. Each of those columns has a differerent set of a conditional formatting rules depending on the category against which the organisation is being assessed. The goal is to be able to tell how many red cells there are in each column and row.

    Yes it is possible to develop a complicated and long counting formula but this is exactly what I am trying to avoid as I will have to do the same sort of analysis against different tables with different rules later on in the process.

    I want to simplify the process and not have to work out the counting formula for each row and column if that is at all possible.

    The Counting conditional formatting colours UDFs that have been suggested by Chip Pearson http://www.cpearson.com/excel/CFColors.htm seem to do what I want however I cant get it to function properly in the workbook I have attached at the start of the post. What I would really like is for someone to help me identify what is wrong with the the three UDFs I copied from Chips website in Module1 of my workbook, namely:
    * GetStrippedValue
    * ActiveCondition
    and
    * CountOfCF

    I believe the issue is in the Active Condition UDF but I cant isolate what it is.

    There is no doubt that in many (most) cases, counting based on a formula rather than conditionally formatting is simpler and quicker but in my case being able to spend the time now and set this up would make the work I am doing on this table and future work on simlar but different tables with different formulas and conditions much much simpler and easier in the long run.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1