+ Reply to Thread
Results 1 to 19 of 19

CountCcolor /GetColorCount not working with Conditional formatting

  1. #1
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    CountCcolor /GetColorCount not working with Conditional formatting

    Hello,

    How do I get CountCcolor or GetColorCount VBA module to work with Conditional formatting? Yes, I did insert the VBA module correctly and saved as macro enabled template.

    Let's say in example: if a person has no date or data on cell then it's highlighted as red (I put =A5:A10="" fill color red on conditional formatting) it works but when I try to count that color by either =CountCcolor(A5:A10,A1) or =GetColorCount(A5:A10,A1) - A1 being referral red color. I tried both vba module code which unfortunately did not work.

    I have attached my sample workbook that doesn't work. Also, I have attached another workbook that works for your review and see what I missed or did wrong. Thank you and much appreciated !

    PS - just side question: Can I do conditional formatting if it's before specific date like i.e. if it's all before 2018 then it would turn red and if it's within 2020.10.31 then stay green and if date is within 3 months from now then yellow? Thanks alot.
    Last edited by Byambadorj; 10-20-2019 at 09:30 AM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: CountCcolor /GetColorCount not working with Conditional formatting

    This code uses Interior.ColorIndex to count colors. However, only permanent formatting changes that property of a cell; conditional formatting does not affect it. Use DisplayFormat.Interior.ColorIndex instead.

    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482
    You have very simple cf rules.
    That can very easy be converted to a formula so you do not need a udf or any vba to get to the same result

    For cell M9 use this formula.

    =COUNTBLANK(M12:M47)+COUNTIF(M12:M47,"RPL")+COUNTIF(M12:M47,"C")

    That will get the right result and does not need the top 3 rows for color reference

  4. #4
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Re: CountCcolor /GetColorCount not working with Conditional formatting

    Quote Originally Posted by Roel Jongman View Post
    You have very simple cf rules.
    That can very easy be converted to a formula so you do not need a udf or any vba to get to the same result

    For cell M9 use this formula.

    =COUNTBLANK(M12:M47)+COUNTIF(M12:M47,"RPL")+COUNTIF(M12:M47,"C")

    That will get the right result and does not need the top 3 rows for color reference
    WHAT? You do understand everyone may have specific changes right? also need to think about the requirement, completed and compliance row. Best way is to count the color...

    Can you please show your way and put it on new tab on the workbook? Would like to see if it works.

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: CountCcolor /GetColorCount not working with Conditional formatting

    You cannot use a UDF to count cells coloured via conditional formatting.
    If you are not willing to use a formula, then you will need a macro.

  6. #6
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Re: CountCcolor /GetColorCount not working with Conditional formatting

    Quote Originally Posted by 6StringJazzer View Post
    This code uses Interior.ColorIndex to count colors. However, only permanent formatting changes that property of a cell; conditional formatting does not affect it. Use DisplayFormat.Interior.ColorIndex instead.

    Please Login or Register  to view this content.
    Tried changing it. It doesnt work. Shows #VALUE! error. Can you edit on the workbook please?

  7. #7
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Re: CountCcolor /GetColorCount not working with Conditional formatting

    Quote Originally Posted by Fluff13 View Post
    You cannot use a UDF to count cells coloured via conditional formatting.
    If you are not willing to use a formula, then you will need a macro.
    UDF count count cells coloured via conditional formatting? Then how did the tab 2 of the attached workbook count that???? I can its counting it correctly. What are your suggestions? I am willing to go for any possibilities to make this work whether its formula or macro? whichever one is easier and better. Thank you. Care to show me how it's done in relevant to the workbook?

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: CountCcolor /GetColorCount not working with Conditional formatting

    There is no tab 2 in the file

    One option is to check for activeconditions, but it's not simple. Have a look here http://www.cpearson.com/Excel/CFColors.htm
    The simplest solution is to use a formula as suggested by Roel in post#3

  9. #9
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Re: CountCcolor /GetColorCount not working with Conditional formatting

    Quote Originally Posted by Fluff13 View Post
    There is no tab 2 in the file

    One option is to check for activeconditions, but it's not simple. Have a look here http://www.cpearson.com/Excel/CFColors.htm
    The simplest solution is to use a formula as suggested by Roel in post#3
    There are 2 tabs on the work - Sample and VBA and Conditional Format Work tabs. Just check the VBA and Conditional Format Work tab and see Required row - CountCcolor formula is working and it counts 36 requirements and it is used with conditional formatting. if you put RPL or C on any red cell it changes.

  10. #10
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482
    Quote Originally Posted by Byambadorj View Post
    Can you please show your way and put it on new tab on the workbook? Would like to see if it works.
    Please see attached workbook.

  11. #11
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Re: CountCcolor /GetColorCount not working with Conditional formatting

    Quote Originally Posted by Roel Jongman View Post
    Please see attached workbook.
    Thanks a lot Roel. Just another thing if I may - is it possible to do conditional formatting like if it's from last year 2018 then red, if it's in the future from now on then green and if it's within 3 months from now then yellow? Thanks alot.

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: CountCcolor /GetColorCount not working with Conditional formatting

    You have obviously uploaded a different file as the original only had one tab.

    If I put c or RPL into M12:AF47 the countA formula results change but the CountCcolor ones don't they say 36 regardless.

  13. #13
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Re: CountCcolor /GetColorCount not working with Conditional formatting

    Quote Originally Posted by Fluff13 View Post
    You have obviously uploaded a different file as the original only had one tab.

    If I put c or RPL into M12:AF47 the countA formula results change but the CountCcolor ones don't they say 36 regardless.
    @Fluff13 - How do I get to count all blanks + if inserted a date or any text or data to show 36? At the moment, its counting 36 because it has formula like +COUNTIF(M12:M47,"RPL")+COUNTIF(M12:M47,"C"). Thanks a lot.

  14. #14
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Re: CountCcolor /GetColorCount not working with Conditional formatting

    Is it possible to count multiple range of blank cells with =COUNTBLANK formula? Like on the attached workbook, on J10, it's currently using =COUNTBLANK(L10:Y10)+COUNTA(L10:Y10). But what if I want to add another range of blank cells like example from AA10:AJ10+COUNTA(AA10:AJ10)? It's not working, there is no countblanks formula. I tried using =COUNTBLANK(L10:Y10)+COUNTA(L10:Y10)+COUNTBLANK(AA10:AJ10)+COUNTA(AA10:AJ10) but it doesnt work. The reason is some people on the training matrix will require additional training as their roles are different. I can't go =COUNTBLANK(L10:AJ10) because there are empty break like area on Column Z.

    Please help. I have attached the workbook for your review. Thanks so much.
    Attached Files Attached Files

  15. #15
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: CountCcolor /GetColorCount not working with Conditional formatting

    I cannot help as
    1) I have no idea what you are trying to do.
    2) Phrases like "but it doesnt work" are less than helpful. In what way doesn't it work?
    3) This formula =COUNTBLANK(L10:Y10)+COUNTA(L10:Y10) makes absolutely no sense to me, you might as well just get rid of the formula & type 14 into the cell, as that is all the formula will ever return

  16. #16
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Re: CountCcolor /GetColorCount not working with Conditional formatting

    Quote Originally Posted by Fluff13 View Post
    I cannot help as
    1) I have no idea what you are trying to do.
    2) Phrases like "but it doesnt work" are less than helpful. In what way doesn't it work?
    3) This formula =COUNTBLANK(L10:Y10)+COUNTA(L10:Y10) makes absolutely no sense to me, you might as well just get rid of the formula & type 14 into the cell, as that is all the formula will ever return
    Did you look at the spreadsheet that I attached on my last post? How can count all blank cells from L10:Y10 and add AA10:AJ10? Thats my question.

  17. #17
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: CountCcolor /GetColorCount not working with Conditional formatting

    Quote Originally Posted by Byambadorj View Post
    Did you look at the spreadsheet that I attached on my last post?
    Yes, and that doesn't help, which is why I responded as I did.

    How can count all blank cells from L10:Y10 and add AA10:AJ10? Thats my question
    Simply use
    =COUNTBLANK(L10:Y10)+COUNTBLANK(AA10:AJ10)

  18. #18
    Forum Contributor
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    Office 2021 Professional Plus
    Posts
    120

    Re: CountCcolor /GetColorCount not working with Conditional formatting

    Quote Originally Posted by Fluff13 View Post
    Yes, and that doesn't help, which is why I responded as I did.

    Simply use
    =COUNTBLANK(L10:Y10)+COUNTBLANK(AA10:AJ10)
    That's exactly what I was looking for. Thanks man. Sorry if I made u angry or frustrated. My bad if i didnt make any sense. English is not my native language im really sorry. Thank you very much.

  19. #19
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: CountCcolor /GetColorCount not working with Conditional formatting

    Glad to help & thanks for the feedback.

    Never angry, or frustrated & don't worry about your English, It's far far better than my (non existent) Mongolian

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. CountCColor and COUNT IF
    By crazymaca69 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-18-2018, 10:37 AM
  2. CountCcolor and COUNTIF(S)
    By BarryGoddard in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-19-2015, 09:29 AM
  3. [SOLVED] Conditional Formatting - Not working.
    By kacey8 in forum Excel General
    Replies: 6
    Last Post: 07-15-2014, 05:52 AM
  4. Conditional Formatting not working?
    By Margate in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-30-2013, 11:31 AM
  5. conditional formatting not working
    By arnab0711 in forum Excel General
    Replies: 3
    Last Post: 06-27-2012, 04:55 AM
  6. Replies: 0
    Last Post: 01-20-2009, 05:19 PM
  7. Several Conditional Formatting only working on some
    By joblinger in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-20-2009, 05:17 PM

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