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  
 
Home > Forums > Excel
 
Excel Forum


Back to 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.
Thanks in advance
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
Hi Adam.

Thanks for the quick reply.

So what I have is a list of PRs (purchase requisitions).
The PR number's colour changes depending on whether there is an Order raised (it goes blue), whether the goods are received (it goes yellow) or if we have received the supplier invoice (it goes orange).

I want to know the following:
Total count of Open PRs
Count of PRs with Orders Placed
Count of PRs with goods received
Count of PRs with invoices received

In other words I have a total of 100 PRs.  50 have had orders placed, 20 are open, 10 are goods received and 20 have had their invoices received.

So I can't just count the field that uses the conditional format because some of them will have met all the criteria so will be counted 3 x under Order Placed, GRN and Invoice received.

Not sure if I am making sense?

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.
 


Back to Excel Forum Comments
 

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.
 
Subscribe
 

You may want to read these articles from the 599CD News:

6/30/2022Sales Chart
6/17/2022Fast AutoSum
2/22/2022ISO Date Format
1/27/2022Work Days
1/11/2022Excel Fast Tips
1/7/2022Count Cells by Color
1/7/2022Excel VBA
1/7/2022Excel Count by Color
12/26/2021Conditional Formatting
12/9/2021Excel 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
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn