I have data appended every day in a table from product specific failures. The data is a table with headers describing the Issues and below are the year to date sum totals of each Issue (row 9).
I want to color condition the cell Issues year totals row 9, Excel will give only 3 conditional formatting, but I need 4 and grouped in sets so I dont know if this can be done with a formula or code.
Note;
This table will be updated every day making the Issues totals a dynamic value.
In this conditional formatting I need the Issues (there are 24 Issues) group rank in 4 colors by their value. To color group the Issues value I need to use this format;
If these were the values in row range J9:AK9
;
24 &This will be the highest value cell color Blue
23 Second group Red
22 Second group Red
21 Second group Red
20 Second group Red
19 Third group Orange
18 Third group Orange
17 Third group Orange
16 Third group Orange
15 Third group Orange
14 Four group Light Yellow
13 Four group Light Yellow
12 Four group Light Yellow
11 Four group Light Yellow
10 Four group Light Yellow
9 Four group Light Yellow
8 Four group Light Yellow
7 Four group Light Yellow
6 Four group Light Yellow
5 Four group Light Yellow
4 Four group Light Yellow
3 Four group Light Yellow
2 Four group Light Yellow
1 Four group Light Yellow
So it comes up to
1 cell Blue highest value
3 Cells Red (next highest value after Blue)
5 Cells Orange (next highest value after Red)
14 Cells Yellow Light (next highest value after Orange)
I have attached a workbook with the Sheet name HeatMap containing data to mimic the range intended to colorcode row 9J:AK9 , and data below row 12 is the data entered every day and is update, Sheet What code should to will explain my intentions for the code to work.
Ill appreciate any input to solve this problem in advance
Start Free Trial
