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

Brott

Board Regular
Joined
Dec 12, 2002
Messages
110
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 :confused:
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Dec 12, 2002
Messages
110
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
Joined
Feb 8, 2002
Messages
11,959
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
Joined
Dec 12, 2002
Messages
110
(y) 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 :cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,645
Members
414,083
Latest member
Mrsash

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
Top