EXCEL Paint a certain amount of cells

To solve this you will need to create some rules. As there is no reference how you did, the following rules become these:

1) Set Chart area and minimum and maximum values :

Let's suppose that the area of your chart comprises the cell A1 (day 1) to A31 (day 31), so each day of the month is represented by the row, and also understand the columns from A to J , which are 10 columns each representing values within a range of 10%, totaling 100% if considered all (from A to J ).

Then the minimum value is 0% and the maximum value is 100% .

2) How to treat each cell in the chart as a function of the percentage value of the day :

In total, the 10 cells from A to J are 100% , so a 34% > on day 1 , it would be represented by the cells A1 , B1 , C1 and D1 > (34% is in the range of up to 40% inclusive). In this case, only these cells should be "painted".

Then the rule would be as follows ("value" corresponds to the percentage value relative to a day ranging from 0% to 100%):

  

Value = 0% = > No cell is painted

     

Value greater than 0% and less than or equal to 10% = Column strong> is painted (first 10%)

     

Value greater than 10% and less than or equal to 20% = Column (10% considering the first 10% of the A column totaling 20%)

... and so on, until:

  

value greater than 90% and less than or equal to 100% = > strong> is painted (all previous ones are also painted in this case, totaling 100%)

3) "Painting" : For your need, we have three states:

a) No value (0%)

b) With value (up to 100% inclusive) and the date of the day greater than the day of the day (for example)

c) With value (up to 100% inclusive) and the date of the day less than or equal to the day indicated

To treat the graph, values will be given to the cells according to the three previous states, respectively:

a) The cell will be 0

b) The cell will be set to 1

c) The cell will have a value of 2

To "paint" the cells respectively the rule will be as follows:

a) White

b) Blue

c) Orange

The orange color indicates the days already running including the day itself, and the blue color indicates the days not yet taken place.

4) Formula to define the chart and its colors :

Before the formula, assuming that the percentage values for each day are in the respective row of column K, and that there is a cell named " Ate_este_day ", with the value of the day pointed to; the formula for the A1 cell that should be copied to all the other cells that represent the graphic, should be:

=SE(E($K1>0;$K1>=Col()*0,10);SE(A1<=Ate_Este_Dia;2;1);0)

Now the graph can already be "perceived", just check the regions with values 1 and 2, then "paint"

After copying, you must select all the cells that belong to the graphic, in this case the A1: J31 banner and include three conditional formatting, in the following options:

Formatar apenas células que contenham

    "Valor da célula"   "é igual a"     0

        Formatar: Preenchimento "Cor Branca"

        Formatar: Fonte "Cor Branca"


Formatar apenas células que contenham

    "Valor da célula"   "é igual a"     1

        Formatar: Preenchimento "Cor Azul"

        Formatar: Fonte "Cor Azul"


Formatar apenas células que contenham

    "Valor da célula"   "é igual a"     2

        Formatar: Preenchimento "Cor Laranja"

        Formatar: Fonte "Cor Laranja"