Count Cells By Color not working with conditional formatting.
I like counting the amount of red cell trigger by conditional formatting. I found a nice script on the internet. It works wel when i color a cal myself. But the cells that are colored with conditional formatting don't get count. Is there a fix or a better script for this problem.
A second software is a no go because of company policy.
Please Login or Register to view this content.
Last edited by Frigide; 01-29-2019 at 03:14 PM.
Try something like:
BTW: Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.Please Login or Register to view this content.
Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
DisplayFormat doesn't work in UDFs, at least as late as Excel 2010.
Entia non sunt multiplicanda sine necessitate
Changed it. I don't know what te do with your line?
Isn's the line already in the script?
Doesn't work in 2013 either.
Is there an work around? Maybe change cell color without conditional formatting
Your original function.
I don't get it. My Original function is not working.
With static formatting applied to both the reference range and the data range?
Suggest you step through the code and watch it execute. See http://www.cpearson.com/excel/DebuggingVBA.aspx
i don't find the problem. I'm no expert! Is it possible to count cells that ar collard by conditional formatting or is this like a hidden color?
To repeat post 3, if a cell is colored by conditional formatting, then a function called from the worksheet (a user-defined function, "UDF") cannot determine the color.
A UDF can determine the color of a cell that has static formatting.
Last edited by shg; 02-01-2019 at 01:55 PM.
Then the only thin i can think of is to make i mirror sheet and =if all the 1100 fields with the same limits like the conditional formatting does. Not only is it way to much work. When we found that other setpoint are better i can change everything again. Not a good idea!
You can use a macro and run it from a command button.
Or your UDF could use the same rule used for conditional formatting and count on that basis.
The only thing i can think of is a extra cell next to the downloaden data compare the dowloaded cell with a other cell and give that extra cell a LL L A H HH en count them. But it wont be good looking.
Something like this:
Please Login or Register to view this content.
Last edited by xladept; 02-01-2019 at 07:42 PM. Reason: Typo
The sheet where i want color counting.
Row K9 to S9 is for counting the rows K9 for K10-K114, L9 for L10-L114 and so on.
E to I is for setting up and lower limits. Every row is different.
Maybe someone can help?
This seems to work:
Please Login or Register to view this content.
It works like a charm!! Tanks. Realy THANKS!!
You're welcome and thanks for the rep!
Stil working very nice!! But i like to protect the sheet. With the other macro's i do this withOriginally Posted by xladept
ActiveSheet.Protect "xxxxx"
ActiveSheet.Unprotect "xxxxx"
But i dont know how to do it with this Macro
Try:
Please Login or Register to view this content.
And again works like a charm!! Thanks!!
You're welcome and thanks for the rep!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks