VBA code for filling a range of cells partially with colours

sjoerd.bosch

New Member
Joined
Feb 9, 2012
Messages
49
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


Screenshot 2023-11-19 192339.png
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
BCDEFGHI
1
2
3PS
4AB  
5115%45%
6245%15%
7350%77%
8488%87%
9545%98%  
10655%47%
11762%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
RangeFormula
F4F4=C5
H4H4=D5
F9F9=C6
H9H9=D6
F14F14=C7
H14H14=D7
F19F19=C8
H19H19=D8
F24F24=C9
H24H24=D9
F29F29=C10
H29H29=D10
F34F34=C11
H34H34=D11
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H34:I38Other TypeDataBarNO
F34:G38Other TypeDataBarNO
H29:I33Other TypeDataBarNO
F29:G33Other TypeDataBarNO
H24:I28Other TypeDataBarNO
F24:G28Other TypeDataBarNO
H19:I23Other TypeDataBarNO
F19:G23Other TypeDataBarNO
H14:I18Other TypeDataBarNO
F14:G18Other TypeDataBarNO
H9:I13Other TypeDataBarNO
F9:G13Other TypeDataBarNO
H4Other TypeDataBarNO
F4:G8Other TypeDataBarNO


And this is the result:
CF Result.jpg
 
Last edited:
Upvote 0
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
BCDEFGHI
1
2
3PS
4AB  
5115%45%
6245%15%
7350%77%
8488%87%
9545%98%  
10655%47%
11762%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
RangeFormula
F4F4=C5
H4H4=D5
F9F9=C6
H9H9=D6
F14F14=C7
H14H14=D7
F19F19=C8
H19H19=D8
F24F24=C9
H24H24=D9
F29F29=C10
H29H29=D10
F34F34=C11
H34H34=D11
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H34:I38Other TypeDataBarNO
F34:G38Other TypeDataBarNO
H29:I33Other TypeDataBarNO
F29:G33Other TypeDataBarNO
H24:I28Other TypeDataBarNO
F24:G28Other TypeDataBarNO
H19:I23Other TypeDataBarNO
F19:G23Other TypeDataBarNO
H14:I18Other TypeDataBarNO
F14:G18Other TypeDataBarNO
H9:I13Other TypeDataBarNO
F9:G13Other TypeDataBarNO
H4Other TypeDataBarNO
F4:G8Other TypeDataBarNO


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.
 
Upvote 0
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...
 
Upvote 0
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

1700479903946.png


.. and this is what my sheet looks like

1700479989798.png


Now I change C3 to 80% and I2 to 4

1700480079093.png


Link to sample file: CF Demo2
 
Upvote 0
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!
 
Upvote 0
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. :)
 
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top