EXCEL Paint a certain amount of cells

Question

I have a spreadsheet that works like a chart of Gantt, that goes from day 01 to day 31, and my doubt is simple, based on the day calculation of an order, 12 days for example, have how I make excel 'paint' only 12 lines, from 01 to 12 for example?

Answer 1

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

1) Set the Chart area and minimum and maximum values:

Let’s assume the area of your graph understands the cell A1 (day 1) until A31 (day 31), so each day of the month is represented by the row number, and also comprises the columns of To until J, that are 10 columns that represent, each, values of a range of 10%, totaling 100% if considered all (of To until J).

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

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

In total, the 10 cells of To to J add up 100%, then a value of 34% on the day 1, would be represented by 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 that goes from 0% to 100%):

Valor = 0% => No cell is painted

Valor greater than 0% and less than or equal to 10% => The column cell To is painted (first 10%)

Valor greater than 10% and less than or equal to 20% => The column cell B is painted (plus 10% considering the first 10% of the column To totaling 20%)

... and so on, until:

Valor greater than 90% and less than or equal to 100% => The column cell J is painted (all the above are also painted in this case, totaling 100%)

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

a) No value (0%)

b) With value (up to 100% inclusive) and the date of the day higher than that of the appointed day (today, for example)

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

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

a) The cell will be set to 0

b) The cell will be set to 1

c) The cell will be set to 2

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

a) White

b) Blue

c) Orange

Where the colour orange indicates the days already running including the day pointed out, and the colour blue indicates the days not yet taken.

4) Formula to define the chart and its colors:

Before the formula, assuming that the percentage values of each day are in the respective rows of column K, and that there is a cell called "Ate_este_dia", with the day value pointed; the cell formula A1 which must be copied to all other cells representing the graph, must 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, missing then "paint"

After copying, you must select all cells that belong to the chart, in this case the track A1:J31 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"

Done! the value per day (when any) will be represented as a graph of Gant with orange "bars" for days already running and blue for days not running.