# Fill in an area/object based on a certain value.

#### Brott

##### Board Regular
I have done this before but I just can not remember how I did it. (Workbook has been deleted)
I have propane tanks, fuel oil tanks, water storage tanks that I have the level being brought into excel (query database) but what I am looking for is a visual add so at a glance an individual can just go to that sheet and see a picture/object of a cylinder, oval shape cylinder and have it shown a different color to represent the current level of the fluids.
Thanks

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### Juan Pablo González

##### MrExcel MVP
Well, I guess there could be a number of ways to do that, just depends on your "drawing" skills and creativity:

- One option is to use charts
- Or use conditinoal formatting on cells that have shapes around them
- Or use VBA to create all the shapes with the appropiate level

#### Andrew Poulsom

##### MrExcel MVP
Add to cylinder shapes to your worksheet. Size and position the first so that it is a full tank and colour it, say red. Add another cylinder shape, but don't worry about it's size or position and leave the colour as automatic. Then run this code:

Code:
``````Sub Test()
With Worksheets("Sheet1")
.Shapes("AutoShape 2").Top = .Shapes("AutoShape 1").Top
.Shapes("AutoShape 2").Left = .Shapes("AutoShape 1").Left
.Shapes("AutoShape 2").Height = .Shapes("AutoShape 1").Height * .Range("A1").Value
End With
End Sub``````

which assumes that cell A1 contains the percent full.

#### Brott

##### Board Regular
I tried putting that code in and at first everthing looked great. However the portion that I want to be shown as full ends up on top. This is actually a little hard to explain. You may already know what I am talking about. I tried mixing the shape numbers around in the code. What I want to see is if I have 65% (full) shown in cell A1 I would like to see the shaded area begin at the bottom and reflect a higher percentage as the color moves up the shape. I tried some other things but no luck. I believe this is what I am looking for but it seems like things are backwards.
Thanks again for any help.

#### Juan Pablo González

##### MrExcel MVP
This is a quick fix on Andrew's code...

<font face=Courier New>
<SPAN style="color:#00007F">Sub</SPAN> Test()
<SPAN style="color:#00007F">With</SPAN> Worksheets("Sheet1")
.Shapes("AutoShape 2").Height = 0
.Shapes("AutoShape 2").Top = .Shapes("AutoShape 1").Top + .Shapes("AutoShape 1").Height
.Shapes("AutoShape 2").Left = .Shapes("AutoShape 1").Left
.Shapes("AutoShape 2").Height = .Shapes("AutoShape 1").Height * .Range("A1").Value
.Shapes("AutoShape 2").Top = .Shapes("AutoShape 2").Top - .Shapes("AutoShape 2").Height
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

#### Brott

##### Board Regular
Thanks that was just what I was looking for. This site really makes me look good. FYI I do give prompts to the site for my progress and to many excel users.

Thanks, until next time

Replies
1
Views
525
Replies
2
Views
721
Replies
0
Views
228
Replies
10
Views
1K
Replies
1
Views
839

1,172,219
Messages
5,879,746
Members
433,454
Latest member
xWiZardx

### 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.

### Which adblocker are you using?

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

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