Resize 2 rectangles based on numerical values but have both contained within one cell

[kidnos]

Hi everyone!
I am trying to build a table in excel (2010). Ultimately, I would like to be able to have two rectangles within a cell that automatically resize based on different numerical values. The tricky part is, I would like to autoshapes to fall within one cell and not overlap.
I have my customers listed across columns and service lines listed in rows.
Currently I have a rectangle within a cell automatically resize based on the amount of revenue I am doing with that customer (for a specific service). I also have a second rectangle to show how much additional revenue I would like to do with that customer.
However, the autoshapes keep overlapping. Is there a way to stop this from happening? I am using the formula below (that I found on the web) for the rectangles. Is there an easier way for me to be thinking about this? I have attached the sheet. Thanks in advance!


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$101" Then
Shapes("Rectangle 1").Width = Target.Value
End If

If Target.Address = "$C$101" Then
Shapes("Rectangle 2").Width = Target.Value
End If
End Sub

 

[SkipVought]

Hi,

each cell/range has the following properties associated with size & location:

Top
Left
Height
Width

You ought to be able to use these values to position & size each shape so that they fit without overlapping.

 

[SkipVought]

hi,

So what's the convention so that the program can identify which rectangles are related to what cell and which one is revenue 1 and revenue 2?

You sure do not want to have a separate set of code for each cell!!!

 

[SkipVought]

Here are more issues. Your 2 values are in 2 different cells. So are the 2 rectangles across 2 cells?

Then size & position are in pixels. So you 2 values will have to size the widths proportionally.

 

[kidnos]

Hi Skip,
Yeah, so those are some of the issues I'm facing, that's why I'm thinking there might be an alternative.
So the two rectangles, which are linked to two values in two cells are expected to be contained in one cell. The size of the one cell for each customer that contains both rectangles is supposed to represent $100 (for example). I might be currently doing $30 with a customer and hope to do another $40 this year. Based on this, the two rectangles will then cover (70/100) 70% of the cell. Alternatively, I might be doing $100 with a customer and then the rectangle would take up the entire cell.
Another thing that's challenging me is, how big do I make the cell for each customer? I was thinking of having all cells equal the size of my current revenue with my largest customer (because that probably represents a max value per customer).

 

[SkipVought]

I'd set up a table with columns for CustomerID, ForecastAmt, YTDAmt and a calculated column for to-go amount. You could do a chart on year-to-date and to-go amounts and plot it as a Percent Chart.

 

[combo]

You can build UDF, for instance:

Public Function FormatShape(ShapeName As String, rng As Range, R, G, B)
With Application.ThisCell.Parent.Shapes(ShapeName)
    .Left = R.Left
    .Top = R.Top
    .Width = R.Width
    .Height = R.Height
    .Fill.ForeColor.RGB = RGB(R, G, B)
End With
End Function

This function has to be used it in the sheet where the shape named as ShapeName value is, rng is the range you need to cover with shape. R, G and B are colour parameters if you need to change colour dynamically.

combo