count by colour (per each line) from a range which has conditional formating

[Danny77]

Dear all,

I need help urgently by a "brilliant guru" in Excel and I am very sure that I will find here.
My issue is following: in attached file I have a table (D3:H12), where the cells have different colors, depending by the value (with conditional formatting). All that I need is to find o formula, able to count per each line, on the right part (N3:R3 aso), the number of the values, per each different color (I had insert manually the values, per each line, on the right part)...

Could you help me please????

Regards!

[gyclone]

FYI, you cannot check for colors created by conditional formatting. In this case, you'd have to loop through each cell and test the cell contents against the conditions you used to add the conditional colors.

[Danny77]

most exactly, what you recommend me?

[gyclone]

See the attached file example

Here is a basic example of the code:

Please Login or Register  to view this content.

[Danny77]

Thanks for your answer...but, certainly, I was not very clear. I will attach my real document and I will try to be more explicit:

Each client has 5 indicators, and each indicator will have a color, depending by the values inserted! But, the value which generate the red color for "Client1Indicator1" is different by the value which generate same color for "Client1Indicator3). AT the end, I must to count/sum, for each client how many red cells are, or purple or etc etc...

Perhaps the solution could be on VBA...

Regards!

[gyclone]

The same principle applies, you just have to change your conditional test for each row (see below).

Please Login or Register  to view this content.

[Danny77]

Hi,

Could you please tell me what I did wrong in the vba code from the xlsm attached? I tried to use your example but I am not sure that I used correct syntax when I define the range values (between x value and y value).

Another question is referring how can I define the color ? For example: for the a indicator I want the orange color to be for 0.5-1 but for b indicator the orange color I want to be defined for the range 100-150. This values are only for example, are dummy data. Should I know some colors codes ? e.g : red color has xyz code..and so on..?

In the example attached you can see that I need only the aggregated results (o16:r20) and not the detailed one (in the right of the page, x1:al24). I make this detailed example to be able to explain how should be computed the final aggregated results.

Please let me know if I explained well what I want to make and what should I change in the vba code.

Regards,

[gyclone]

Hi,

You should only use "Case" at the beginning of each condition. You were duplicating after "and" in your conditions (see example below and attached file).

Crude example:

Please Login or Register  to view this content.

Instead of:

Please Login or Register  to view this content.

Also, you were missing an "End Select" statement.

I'm afraid I'm not sure what you're asking about the colors. You can assign colors a number of ways, using Excel Color Codes, Excel Color Indexes, rgb color codes, etc. Google "Excel colors" and you can find the corresponding color codes.

[Danny77]

finally, I solved!!! Thanks a lot for your help, you are very good!
D