VBA code for filling a range of cells partially with colours

[sjoerd.bosch]

Hi
I am looking for a code what fills the ranges of cells P1, S1, P2, S2, etc partially - entering the values in range cells C5 for P1, D5 for S1, etc -
with the colours listed in cells C14:C20 - the colours are defines in the range C23:C36
result should look P1 15% filled and colour orange
If I change cell C23 to 5 - it should change to yellow
If I change value in C5 to 50 - fill should be 50%
etc

I managed to get it done with Sparklines - but it does not completely cover the range of cells
Tried with shapes - which I cannot manage

Any ideas - solution are very much appreciated

 

[kevin9999]

Is there any reason why you don't just use Conditional Formatting for this? In P1 you could enter "=C5" and so on, then set the Format for all your P & S cells to Custom Format ;;; (3 semicolons) which would hide the values. Then with Conditional Formatting:
New Rule
Rule Type: Format all cells based on their values
Format Style: Data Bar
Type: set both Minimum & Maximum to Number
Value 0 (min) and 1 (max)
Bar Appearance: Solid Fill
select your colour of choice
XL2BB doesn't show CF colored cells, but this is the layout:

Book1
	B	C	D	E	F	G	H	I
1
2
3					P		S
4		A	B
5	1	15%	45%
6	2	45%	15%
7	3	50%	77%
8	4	88%	87%
9	5	45%	98%
10	6	55%	47%
11	7	62%	76%
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Sheet2

Cell Formulas
Range	Formula
F4	F4	=C5
H4	H4	=D5
F9	F9	=C6
H9	H9	=D6
F14	F14	=C7
H14	H14	=D7
F19	F19	=C8
H19	H19	=D8
F24	F24	=C9
H24	H24	=D9
F29	F29	=C10
H29	H29	=D10
F34	F34	=C11
H34	H34	=D11

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H34:I38	Other Type	DataBar		NO
F34:G38	Other Type	DataBar		NO
H29:I33	Other Type	DataBar		NO
F29:G33	Other Type	DataBar		NO
H24:I28	Other Type	DataBar		NO
F24:G28	Other Type	DataBar		NO
H19:I23	Other Type	DataBar		NO
F19:G23	Other Type	DataBar		NO
H14:I18	Other Type	DataBar		NO
F14:G18	Other Type	DataBar		NO
H9:I13	Other Type	DataBar		NO
F9:G13	Other Type	DataBar		NO
H4	Other Type	DataBar		NO
F4:G8	Other Type	DataBar		NO

And this is the result:

 

[kevin9999]

Here's a link to the demo file described in the previous post: Loading Google Sheets

 

[sjoerd.bosch]

Is there any reason why you don't just use Conditional Formatting for this? In P1 you could enter "=C5" and so on, then set the Format for all your P & S cells to Custom Format ;;; (3 semicolons) which would hide the values. Then with Conditional Formatting:
New Rule
Rule Type: Format all cells based on their values
Format Style: Data Bar
Type: set both Minimum & Maximum to Number
Value 0 (min) and 1 (max)
Bar Appearance: Solid Fill
select your colour of choice
XL2BB doesn't show CF colored cells, but this is the layout:

Book1
	B	C	D	E	F	G	H	I
1
2
3					P		S
4		A	B
5	1	15%	45%
6	2	45%	15%
7	3	50%	77%
8	4	88%	87%
9	5	45%	98%
10	6	55%	47%
11	7	62%	76%
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Sheet2

Cell Formulas
Range	Formula
F4	F4	=C5
H4	H4	=D5
F9	F9	=C6
H9	H9	=D6
F14	F14	=C7
H14	H14	=D7
F19	F19	=C8
H19	H19	=D8
F24	F24	=C9
H24	H24	=D9
F29	F29	=C10
H29	H29	=D10
F34	F34	=C11
H34	H34	=D11

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H34:I38	Other Type	DataBar		NO
F34:G38	Other Type	DataBar		NO
H29:I33	Other Type	DataBar		NO
F29:G33	Other Type	DataBar		NO
H24:I28	Other Type	DataBar		NO
F24:G28	Other Type	DataBar		NO
H19:I23	Other Type	DataBar		NO
F19:G23	Other Type	DataBar		NO
H14:I18	Other Type	DataBar		NO
F14:G18	Other Type	DataBar		NO
H9:I13	Other Type	DataBar		NO
F9:G13	Other Type	DataBar		NO
H4	Other Type	DataBar		NO
F4:G8	Other Type	DataBar		NO

And this is the result:
View attachment 102174

Hello,
Many thanks for your reply.
Would be perfect if the bars were vertical.
But I didn't see an option for that, which is why I used sparklines before. But they do not fill up the entire cell.

 

[kevin9999]

Would be perfect if the bars were vertical.

Like you, I couldn't find a way of doing that. You can take a picture of each of the merged areas, then rotate the image, but they wouldn't change if the values in your data changed...

 

[Peter_SSs]

Would something like this be any use? It would take a lot of setting up.

I have rearranged the sheet for this test. You can change it if you want. I have only done one area and only partially at that.

1. Does it have to be merged cells of 2 columns and 5 rows? Could it instead be 1 column and 100 rows (each of height 1) with nothing merged as I have used below?
2. I have turned off the sheet gridlines & would just use borders where required. For example I have all borders in the 2 tables at the top and a border around the outside of F11:F110
3. Set up Conditional Formatting along the lines I have (a couple of colours still to add). The colours you have in your B14:C20 would have to be manually associated with the numbers directly in the CF set-up.

XL2BB will not deal with the very small rows so I will show images and have provided a link to the sample file below.
Here are 5 of the CF rules

.. and this is what my sheet looks like

Now I change C3 to 80% and I2 to 4

Link to sample file: CF Demo2

 

[sjoerd.bosch]

Here's a link to the demo file described in the previous post: Loading Google Sheets

Hi. Can't get the form. Access not allowed.

 

[sjoerd.bosch]

Would something like this be any use? It would take a lot of setting up.

I have rearranged the sheet for this test. You can change it if you want. I have only done one area and only partially at that.

1. Does it have to be merged cells of 2 columns and 5 rows? Could it instead be 1 column and 100 rows (each of height 1) with nothing merged as I have used below?
2. I have turned off the sheet gridlines & would just use borders where required. For example I have all borders in the 2 tables at the top and a border around the outside of F11:F110
3. Set up Conditional Formatting along the lines I have (a couple of colours still to add). The colours you have in your B14:C20 would have to be manually associated with the numbers directly in the CF set-up.

XL2BB will not deal with the very small rows so I will show images and have provided a link to the sample file below.
Here are 5 of the CF rules

View attachment 102191

.. and this is what my sheet looks like

View attachment 102192

Now I change C3 to 80% and I2 to 4

View attachment 102193

Link to sample file: CF Demo2

Dear Peter,
Also started to do that, but it it a tremendous amount of data to fill in.
Was hoping that a VBA would do the job, but it seems that it is just not possible.
Thanks anyways!

 

[Peter_SSs]

but it it a tremendous amount of data to fill in.
Was hoping that a VBA would do the job, but it seems that it is just not possible.

vba would likely be possible, but also would likely take a tremendous amount of setting up.