+ Reply to Thread
Results 1 to 9 of 9

Counting Cells Colored by a Conditional Format

  1. #1
    Registered User
    Join Date
    01-24-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    11

    Counting Cells Colored by a Conditional Format

    Hi there,

    I need someones help please. I have been trawling the internet and trying out all the available VBA coding to count cell colors changed by a conditional format. I have tried using the VBA from C Pearson and have also tried using one from Mr Excel.

    When i copy and paste the VBA code into my workbook and then use the relevant formula to pull the data out I want, instead of it counting only the red, green or amber colored cells it is counting all of them within the range irrespective of me putting "1, 2 or 3" as the criteria.

    I am using MS Excel 2010, however, I have also tried doing this in MS Excel 2003.

    If you are able to help and would like a copy of the workbook just let me know.

    Regards

    Michael

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Counting Cells Colored by a Conditional Format

    A copy of the workbook would be useful, please.

  3. #3
    Registered User
    Join Date
    01-24-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Counting Cells Colored by a Conditional Format

    Am I able to attach it to a reply?

  4. #4
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Counting Cells Colored by a Conditional Format

    Yes, click on the "Go advance" button, and then use the paper-clip icon to attach your file.

  5. #5
    Registered User
    Join Date
    01-24-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Counting Cells Colored by a Conditional Format

    Andrew,

    The file is attached.

    Look forward to your response.

    Michael
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    10-02-2012
    Location
    Baku
    MS-Off Ver
    Excel 2010
    Posts
    273

    Re: Counting Cells Colored by a Conditional Format

    hi

    to count green: =SUM(--(D3:D117>=E3:E117))
    to count red : =SUM(--(D3:D117<(E3:E117-6)))
    to count orange : =SUM(--(D3:D117<E3:E117))-D126 (D16 is number of red cells)

    All above is an array formula

  7. #7
    Registered User
    Join Date
    01-24-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Counting Cells Colored by a Conditional Format

    Thanks for your reply, however, it has not worked. It comes up with #Value!

  8. #8
    Registered User
    Join Date
    10-02-2012
    Location
    Baku
    MS-Off Ver
    Excel 2010
    Posts
    273

    Re: Counting Cells Colored by a Conditional Format

    Quote Originally Posted by Mike001 View Post
    Thanks for your reply, however, it has not worked. It comes up with #Value!
    After entering the formula pres Ctrl+Shift+Enter
    Not only enter. Because its an array formula

  9. #9
    Registered User
    Join Date
    01-24-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Counting Cells Colored by a Conditional Format

    Quote Originally Posted by eisayew View Post
    After entering the formula pres Ctrl+Shift+Enter
    Not only enter. Because its an array formula
    Thank you this has worked. Will this work in a row as well? I have just noticed that it is also counting the blank cells, is there a way around this so that the formula ignors any blank cells?
    Last edited by Mike001; 01-25-2013 at 06:17 AM.

  10. #10
    Registered User
    Join Date
    01-24-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Counting Cells Colored by a Conditional Format

    Andrew-R, have you had any joy as yet?

    Regards

    Michael

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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