Hi there,
I want Excel to count coloured cells for me. I've got this code but it seems it doesn't work when the colour is changed due to conditional formatting.
Additionally, it doesn't refresh when the colours are getting updated.
Any help is greatly appreciated.
Please Login or Register to view this content.
Cheers,
Kapi
Re: Count coloured cells with conditional formatting
Hi,
try with DisplayFormat but on my side under old Excel versions can't work within such function …
Hi mate,
Could you please provide some further details on how this could be done?
Using DisplayFormat property like you can see in VBA help works on my side in a Sub procedure
but not with your function so the VBA procedure should be launched via a button or via a Worksheet event …
Re: Count coloured cells with conditional formatting
If that can't work neither on your side via DisplayFormat then
via your function do not compare the colors but the values obviously …
Edit : In fact VBA seems useless to compare values just using directly cells formulas !
Last edited by Marc L; 08-25-2023 at 08:27 PM.
I'm sorry but I'm still confused.
Could someone clarify?
As the fill colors are produced by conditional formatting, the following formulas could be used:
In cell B5 for counting green: =COUNTIFS(C2:Z2,"<>")
In cell B6 for counting red: =COUNTIFS(C1:Z1,"<"&A2)-C5
In cell B7 for counting amber: =SUMPRODUCT((C1:Z1<>"")*(C1:Z1-2500<A2))-SUM(C5:C6)
Note that the formulas may be modified to include as many columns as needed.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
He means return the colour produced by the conditional formatting using the DisplayFormat property, like this:Originally Posted by kapi98
But you can't use this in a function (you will get a recursive call loop that will error out) so you have to put it in a Sub and call that using a button or worksheet change event.Please Login or Register to view this content.
MatrixMan.
--------------------------------------
If this - or any - reply helps you, remember to say thanks by clicking on *Add Reputation.
If your issue is now resolved, remember to mark as solved - click Thread Tools at top right of thread.
Re: Count coloured cells with conditional formatting
Yes but as I wrote in my previous post, VBA is useless just using formulas like demonstrated within post #7 …
It worked just fine, thanks for your help. Reputation's been added!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks