Solved: Select case for conditional formatting to build heat chart?

[IgnBan]

Good day everybody. I hope everybody day is going well.

I’m using Excel 2003

I’m in the process of building a 5 color “Heat Map” of certain issues failing in a product. I’ve researched it, goggled it, and could find what I need, so I’m posting here to have ideas.
What I got is a set of dates with the number of times the product predefine issues fail, and what I want is to color code these cells totals to the number indicated in the legend. I got 5 color codes for the failing mode and 24 issues so breaking down the colors in the failing issues I want to group them ;
1st color (blue) 1 cell to be No.1 fail and be 4% of the failing Issues.
2nd color (red) 3 cells to be No. 2 fail and be 13% of the failing Issues.
3rd color (gold) 5 cells to be No. 3 fail and be 21% of the failing Issues.
4th color (light yellow) 15 cells to be No. 4 fail and be 63% of the failing Issues.
The column where the daily total scores are has at the end a total that can be use to do the percentages of Issues and possibly the key to color conditionally format the cell shading.
On the Sheet 2 in the attached workbook has a drawing of the intended number of color that need to be use in the Heat Map .
I was going to start using conditional formatting, but I found that it can not be done more than 3 times. Can this be done any other way?


Thanks in advance for any help.

Edit by Lucas as Jose's request: percentages part corrected.

[lucas]

Try this, it works for many different colors in conditional formatting.

colors are Blue = 5
red = 3
gold = 44
yellow = 6

http://vbaexpress.com/kb/getarticle.php?kb_id=90

[IgnBan]

Lucas, thanks for the link, I think is what I was looking for in the formatting area. Now I’m trying to structure how to apply this color formatting to the range.
I have another question; How can I extract the groups as I show in the sample workbook?
I was thinking extract the max, median and min values of the range, but I don’t thing will work since I need to have the cell coloring grouped as I describe it on the workbook sample; (with in the 24 cells values)

The highest value of all Blue 1 cell, then the next 3 cell values less than the greatest value Red, and the next 5 cells less than the last lowest value of color Red colored Gold and the rest left Light Yellow. I don’t know how to capture the dynamic less value of the previous color value to apply the color to the next down color set.


Or there may be a simpler way to do it?

Can somebody help?

[lucas]

Ignban, it would help if I understood a little more what your are trying to do. I am looking at the sheet ColorCodeBreakdown. Is that the sheet where we are going to try to implement the code? If so why is there only one blue and more of the other colors.

[IgnBan]

Lucas, thanks again for replaying. I made another sample workbook I hope this explain better what I'm trying to accomplish.
All I need is rank the cell values and color code them, but I want specific number of cells colored according to their rank.

Thanks again!

[lucas]

Let me know if I got close Ignban. You have to add an new number or change one of the existing ones to get it to fire.


the code is in the code for the sheet, not in a standard module.

[lucas]

but I want specific number of cells colored according to their rank.

I just saw this part but you should be able to tweak it to get your desired result....if not post back.

[IgnBan]

Lucas thanks for you input. The way you color coded the range is the future intent after couple of year collecting data, but right now the main thing is row 7. I conditionally formatted row 7 to mimic what I want to do.

If you take a looks at Sheet3 in attached workbook it may explain better what I'm trying to accomplish.
In row 7 need to color code according to legend in a breakdown/ranking like this;

1st color (blue) 1 cell to be No.1 fail and be 4% of the failing Issues.
2nd color (red) 3 cells to be No. 2 fail and be 13% of the failing Issues.
3rd color (gold) 5 cells to be No. 3 fail and be 21% of the failing Issues.
4th color (light yellow) 15 cells to be No. 4 fail and be 63% of the failing Issues.

Row 7 is the sum values of Row 27 down.

Thanks for you input,
I hope Sheet3 explain better my objective.

[lucas]

Will the data always go into row 6 or does that vary?

[IgnBan]

The data will always be in row 7, Sheet "HeatMap" is the working copy. Sheet3 is only a sample to explain the need. In Sheet "HeatMap" the row7 is the one need it to have the conditional formatting .

PS
The data in Sheet "Heat Map" row7 is constantly every day changing as data is appended below row 27.

[lucas]

Jose, the decimals are playing tricks on me so I will have to tinker with this to see if I can get it to do what you desire. It would help immensly if we could get rid of the decimals...maybe round up or down in the cell formula's.....would that be a problem?

[IgnBan]

Steve, the posted workbooks were formatted to have no decimals in any cells. No, decimal shouldn't be any problem.

Steve Thanks for your help!

[lucas]

Hey Jose cell C6 in the heatmappostfinal from post #8 has one for example.

ie[vba]172.289683815563[/vba]

that's on sheet 3. you have formula's in your main sheet maybe we could round them if that is ok.

[lucas]

Try this one Jose

[IgnBan]

Thanks Lucas, I'll tweak it from here.

Thanks for all your help.