Excel 2010-2019 Excel 2007 Excel 2003 Tips & Tricks Excel Forum Course Index CIG Excel Book

 Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon More... What's New? Popular Links ------------ Access Courses Access Index Access TechHelp Access Forum ------------ Excel Courses Excel Index ------------ Code Vault Testimonials

 Home > Forums > Excel
Excel Forum
Count Cells by Color Upload Images   Link
Lisa Ker
5 months ago
Hi All
Happy New Year to everyone!
I have an Excel Sheet where I use conditional formatting to change the colour of a row of cells depending on the values of other cells in the Worksheet.
Now I want to count those cells based on their colour.
I Googled and got a VB code which I added to the sheet but that is giving me an error of "No Colour" when I insert the function.
Has anyone else encountered this before?
I have tried to troubleshoot this myself but am not smart enough to figure it out :(
I am happy to share the sheet with whoever is prepared to help so they can pick up what I have done wrong.
 Adam Schwanz 5 months ago Why count the color? Why not just count the fields with the criteria that is the same criteria that sets a color? If Red is a number >5 then just count the numbers >5 instead of counting how many reds.
 Adam Schwanz 5 months ago Could you share an image? Maybe I'm misunderstanding what you're doing. Snip & Sketch
 Lisa Ker 5 months ago
 Scott Axton 5 months ago Based on the picture it appears to me that your color is using the status field (Column) to determine the color. Correct? Even though your PR may have gone through many steps, a PR can only have one current status. True? You need to have 4 counters based on your description above.   Opn, Ord, Gds, and Inv. If Status = "Open" then    Opn = Opn + 1 End If If Status = "Order" then    Ord = Ord + 1 End If If Status = "Goods" then    Gds = Gds + 1 End If If Status = "Invoice" then    Inv = Inv + 1 End If So using the screenshot Ord = 3 and  Inv = 2     Is that what you want?
 Richard Rost 5 months ago Hi Lisa. One of my other students just asked me about this a few days ago. I've got some VBA code to count cells by their color. I'll put it together in a Fast Tip video. Haven't done one for Excel in a while. Stay tuned...
 Richard Rost 5 months ago Here ya go... Excel Count by Color
 Lisa Ker 5 months ago @ Scott Axton You have it exactly right.
 Lisa Ker 5 months ago @Richard Rost You guys are amazing and so fast! I don't know what I'm doing wrong, but it simply counts all the cells and not the cell by colour.
 Richard Rost 5 months ago Well, let's see your code.
 Lisa Ker 5 months ago
 Richard Rost 5 months ago Yeah, code looks good. Let's see the sheet with the formula in the formula bar.
 Lisa Ker 5 months ago
 Lisa Ker 5 months ago @Richard Rost The cell location where the Sample Colours are is on another sheet in the workbook called "Sample" Also, the top area of the range will always be A10 but the bottom will keep increasing which is why no \$ at the second cell range number.
 Richard Rost 5 months ago Yeah, neither of those two things should matter. Try putting them together on one sheet and see what happens. MsgBox the color values and make sure they match. I'd really have to see your workbook file in order to tell you for sure.
 Lisa Ker 5 months ago Hi @Richard Rost Would it be possible for me to email you the sheet? I am getting nowhere fast.
 Kevin Robertson 5 months ago Lisa, Send your sheet to me if you want and I can take a look. I can't guarantee anything as I'm no expert, but I will try to get it working for you. fivexfivepublish@outlook.com
 Lisa Ker 5 months ago Thanks so much @Kevin Robertson. Email sent.
 Kevin Robertson 5 months ago Lisa, Like I said I am no expert, but I did some testing and was able to get the correct values with static Fill Colors but couldn't get it to work with Conditional Formatting. Maybe Richard to give some insights into this issue.
 Richard Rost 5 months ago Lisa, I normally don't accept file submissions, but go ahead and send me your sheet. amicron@gmail.com
 Lisa Ker 5 months ago Much appreciated Richard. I definitely owe you one
 Richard Rost 5 months ago Yeah... I spent about half an hour going over this. Honestly, I'm not sure why it's not working. I'm not getting an error message, but I'm getting the wrong count.
 Richard Rost 5 months ago
 Richard Rost 5 months ago If I MsgBox the TargetCell, I get nothing... which tells me it's not properly sending the range value for the target cell. If I put that code into ANY other workbook I have, it works just fine, so this tells me there's got to be some other code in your workbook that's conflicting with mine. Try it in a blank new workbook without any other code and I bet it works.
 Richard Rost 5 months ago I would suggest moving your data over to a new blank workbook. Set up the CountByColor code. See if it works (and I bet it does). Then pull over any other code you need for functionality, one sub at a time, and that will tell you what's conflicting. I see a lot of other stuff in your VBA... ribbon code and such... and any of that could be the problem.
 Lisa Ker 4 months ago Thank you so much Richard for your efforts.  I will give it a shot. Apologies for the late reply - it has been a nightmare of a month!

This thread is now closed. If you wish to comment, start a NEW discussion, below.

Start a NEW Conversation
 Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.

 You may want to read these articles from the 599CD News:
 6/30/2022 Sales Chart 6/17/2022 Fast AutoSum 2/22/2022 ISO Date Format 1/27/2022 Work Days 1/11/2022 Excel Fast Tips 1/7/2022 Count Cells by Color 1/7/2022 Excel VBA 1/7/2022 Excel Count by Color 12/26/2021 Conditional Formatting 12/9/2021 Excel 2003 Quick Start 1

 Learn  Access - index Excel - index Word - index Windows - index PowerPoint - index Photoshop - index Visual Basic - index ASP - index Seminars More... Customers  Login My Account My Courses Lost Password Memberships Student Databases Change Email Info  Latest News New Releases User Forums Topic Glossary Tips & Tricks Search The Site Code Vault Collapse Menus Help  Customer Support Web Site Tour FAQs TechHelp Consulting Services About  Background Testimonials Jobs Affiliate Program Richard Rost Free Lessons Mailing List Order  Video Tutorials Handbooks Memberships Learning Connection Idiot's Guide to Excel Volume Discounts Payment Info Shipping Terms of Sale Contact  Contact Info Support Policy Email Richard Mailing Address Phone Number Fax Number Course Survey