+ Reply to Thread
Results 1 to 10 of 10

Count colored cells by conditional formatting

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,777

    Count colored cells by conditional formatting

    Hello everyone
    I have a UDF that is supposed to count a range of conditional formatting and there is a weird behaviour
    The udf works fine in the 'Test' macro but as for the worksheet function usage it gives a VALUE error ( the yellow cell) .. any idea how to fix that?

    This thread posted before at this link
    http://www.eileenslounge.com/viewtop...nal+formatting

    Thanks advanced for any help
    Attached Files Attached Files
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  2. #2
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Count colored cells by conditional formatting

    Good afternoon YasserKhalil

    Your respondent at eileenslonge.com was correct - conditional formatting colours within Excel are not exposed to the VBA object model, so can't be detected.

    You will need to determine what conditions change the cell's colour, and test for that condition.
    You don't even have to do it in a "one-step" formula - you could use a helper cell with a decision making formula that will read, say, 1 if CF turns cell blue, 2 for red, 3 for green etc and then test for that.

    I admit, it's not ideal but it's about the only way if you're going to use CF.

    HTH

    DominicB
    Please familiarise yourself with the rules before posting. You can find them here.

  3. #3
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,777

    Re: Count colored cells by conditional formatting

    Thanks a lot for reply
    What if the code used in standard module not a UDF. Will it be easier to deal with the conditional formatting cells in that case ..? or the issue would still the same to be difficult

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    8,680

    Re: Count colored cells by conditional formatting

    You can convert the UDF to standard code & use it quite happily.
    You'll just have to decide, how to set the ranges & the Flag
    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,777

    Re: Count colored cells by conditional formatting

    That's great. Thanks a lot
    So no hope to count or sum cells with CF using UDFs

    Thanks a lot anyway for great and awesome help
    Best Regards

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    8,680

    Re: Count colored cells by conditional formatting

    Not something I've ever used, but you can have a look at Chip's site http://www.cpearson.com/excel/cfcolors.htm

    Thanks for the rep

  7. #7
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,777

    Re: Count colored cells by conditional formatting

    Thanks a lot for the link
    I tried to get how it works but it seems difficult for me. i couldn't apply the solution

  8. #8
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,777

    Re: Count colored cells by conditional formatting

    Hello again
    after I have searched a lot I could find this UDF
    Please Login or Register  to view this content.
    And for count I used this formula
    Please Login or Register  to view this content.
    As excel experts, can you please check this UDF and see if it is OK ...?
    Hope you can test it and see if there are any errors or are there any possible improvements ..?

    Thanks advanced for any kind of help

  9. #9
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,777

    Re: Count colored cells by conditional formatting

    Please can you test that UDF on several cases of CF ..?

  10. #10
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,777

    Re: Count colored cells by conditional formatting

    And this is the final version for those who are interested to test this UDF
    Please Login or Register  to view this content.

+ 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. Sum/ Count colored cells based on conditional formatting
    By V.Cell in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-19-2018, 06:37 AM
  2. How do I Count Cells Colored with Conditional Formatting?
    By PixelPug in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-31-2017, 04:28 AM
  3. How to count colored cells with conditional formatting
    By DJFISH614 in forum Excel General
    Replies: 8
    Last Post: 08-09-2016, 06:06 PM
  4. Replies: 6
    Last Post: 06-07-2016, 01:50 AM
  5. [SOLVED] Count colored cells for conditional formatting
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-08-2015, 05:52 AM
  6. Replies: 1
    Last Post: 11-09-2013, 02:43 AM
  7. Count Cells colored by conditional formatting
    By sam99 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-19-2013, 02:58 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