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

[Brott]

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

 

[Juan Pablo González]

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]

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:

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]

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]

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]

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