Count cells conditionally formatted by colour

Sadly I don't think this is as easy as you'd expect. Excel/vba will not recognize the conditionally formatted color as the true color of the cell. E.g. try

Please Login or Register  to view this content.

and they won't match up. Otherwise, the code below would work fine. Maybe someone has another idea but the only way I can see of making this work is having a vba code that evaluates the conditional formatting criteria directly, and pastes the colour as the true colour of the cell.

Please Login or Register  to view this content.

See these threads.
https://www.excelforum.com/excel-pro...nditional.html
https://www.excelforum.com/excel-pro...by-colour.html

Wasn't aware of the .DisplayFormat trick thanks @ByteMarks. With that edit this will actually work then.

Please Login or Register  to view this content.

With the output in column "AR".

How about

Please Login or Register  to view this content.

Maybe

Please Login or Register  to view this content.

Sorry, I forgot to mention that I am new to VBA. How can I call the function from the worksheet?

I'm not sure that you can.
I don't think you can use DisplayFormat inside a function.

Why not use a formula to count the number of instances where the CF formula returns TRUE for each color?

Originally Posted by 63falcondude

Why not use a formula to count the number of instances where the CF formula returns TRUE for each color?

I am trying to do it the way you are suggesting, but the solution does not seem obvious. My goal is to make a "+" if the majority of the cells within each 6x1 range is green, "o" if yellow, and "-" if red. The CF formulas are in the top right corner of the worksheet. If you can point me in any direction, it would be great. Otherwise, I might consider posting a new thread. Thank you anyway.

I think that is absolutely possible. If you manually colored the cells, you would need VBA but since they are colored using Conditional Formatting, we can use those same rules in a formula.

I'll have a go at it.

Where would you like those "+", "o", or "-" marks to be located? What if there is a tie such as 3 green and 3 yellow?

I am not suggesting that this is a solution, just showing how to count colors that were filled using Conditional Formatting.

Let's take G19:G24 as an example. You can use formulas like these to count the # of red, yellow, and greens for each 6x1 range:

r =SUMPRODUCT(--(ABS(E19:E24-$B19:$B24)>3*G19:G24))
y =SUMPRODUCT((ABS(E19:E24-B19:B24)>2*G19:G24)*(ABS(E19:E24-B19:B24)<3*G19:G24))
g =SUMPRODUCT(--(ABS(E19:E24-$B19:$B24)<2*G19:G24))

Getting on to the question at hand...

A question that I have is which 6x1 ranges should we be looking at? Let's say for cell AR14 (in the workbook from post #13).
I see that cell AR14 is for ContD (S=90) and M=10,000. Which 6x1 range should this look at? G5:G10 or J5:J10?

Great! I will look into the formulas. To answer your question, I just updated the spreadsheet in the previous post with separate columns for OLS, tikh and CART. CART has only one cell, so it should show the result for that specific cell.

You don't need to do all of them, but if you could show me how it works it would be great!

I think this is going to be step 1. See columns AQ:AV in the following attachment.

This gets the color data in a readable format.
This is the most time consuming part but once this is done, part 2 will be creating a single formula to be placed in AZ14 that can be dragged across and down.

It's cumbersome (but not impossible) because of the layout of the data.

That's all the time that I have today though. Enjoy your weekend!

Okay, here would be step 2. All that you have to do is finish the table in columns AQ:AV and then adjust the ranges of the formula in AZ14.

Once you finish the table, the N/A's will go away.

See attachment.

I have been trying different ways to solve this problem for most of my day, but with your help I will finally be able to finish it. Thank you very much for your effort!

You're welcome. Happy to help.

Thanks for the rep!