+ Reply to Thread
Results 1 to 5 of 5

proportionally fill a cell

  1. #1
    M John
    Guest

    proportionally fill a cell

    Is this possible:

    I'm looking for a way to proportionally fill a cell based on the contents.
    For example, if the contents of four rows are:

    ..8
    ..75
    ..5
    1

    then the contents of these cells are 4/5, 3/4, 1/2, and completely
    full....(either top to bottom or left to right).

    Right now I'm using a stacked bar graph, but lining up the axes of the graph
    on top of the the cell gridlines is quite a challenge and very time
    consuming. I'm not optimistic, but thought I would put this question out
    here to check.

    Many thanks in advance.

    MJohn

  2. #2
    Tim Williams
    Guest

    Re: proportionally fill a cell

    Option Explicit

    Sub Macro1()

    Dim c As Range
    Dim v As Single
    Dim s As Shape

    For Each c In Selection
    If c.Value >= 0 And c.Value <= 1 Then

    v = c.Value
    Set s = ActiveSheet.Shapes.AddShape(msoShapeRectangle, _
    c.Left, c.Top, c.Width * v, c.Height)
    s.Fill.Visible = msoTrue
    s.Fill.ForeColor.SchemeColor = 44
    s.Fill.Transparency = 0.67
    End If
    Next c
    End Sub

    --
    Tim Williams
    Palo Alto, CA


    "M John" <[email protected]> wrote in message news:[email protected]...
    > Is this possible:
    >
    > I'm looking for a way to proportionally fill a cell based on the contents.
    > For example, if the contents of four rows are:
    >
    > .8
    > .75
    > .5
    > 1
    >
    > then the contents of these cells are 4/5, 3/4, 1/2, and completely
    > full....(either top to bottom or left to right).
    >
    > Right now I'm using a stacked bar graph, but lining up the axes of the graph
    > on top of the the cell gridlines is quite a challenge and very time
    > consuming. I'm not optimistic, but thought I would put this question out
    > here to check.
    >
    > Many thanks in advance.
    >
    > MJohn




  3. #3
    M John
    Guest

    Re: proportionally fill a cell

    That's beautiful. Simple. Perfect.

    Uhhh-Mayzing.

    Many thanks,
    MJohn

    "Tim Williams" wrote:

    > Option Explicit
    >
    > Sub Macro1()
    >
    > Dim c As Range
    > Dim v As Single
    > Dim s As Shape
    >
    > For Each c In Selection
    > If c.Value >= 0 And c.Value <= 1 Then
    >
    > v = c.Value
    > Set s = ActiveSheet.Shapes.AddShape(msoShapeRectangle, _
    > c.Left, c.Top, c.Width * v, c.Height)
    > s.Fill.Visible = msoTrue
    > s.Fill.ForeColor.SchemeColor = 44
    > s.Fill.Transparency = 0.67
    > End If
    > Next c
    > End Sub
    >
    > --
    > Tim Williams
    > Palo Alto, CA
    >
    >
    > "M John" <[email protected]> wrote in message news:[email protected]...
    > > Is this possible:
    > >
    > > I'm looking for a way to proportionally fill a cell based on the contents.
    > > For example, if the contents of four rows are:
    > >
    > > .8
    > > .75
    > > .5
    > > 1
    > >
    > > then the contents of these cells are 4/5, 3/4, 1/2, and completely
    > > full....(either top to bottom or left to right).
    > >
    > > Right now I'm using a stacked bar graph, but lining up the axes of the graph
    > > on top of the the cell gridlines is quite a challenge and very time
    > > consuming. I'm not optimistic, but thought I would put this question out
    > > here to check.
    > >
    > > Many thanks in advance.
    > >
    > > MJohn

    >
    >
    >


  4. #4
    Tom Ogilvy
    Guest

    RE: proportionally fill a cell

    You want to select some quantity of cells and fill them with different colors
    to make them look like a stacked bar graph based on the values in some other
    cells?

    How many cells?

    Do you mean 4 cells and you want to adjust the height or width?

    Anyway, more information might garner a different response.

    --
    Regards,
    Tom Ogilvy


    "M John" wrote:

    > Is this possible:
    >
    > I'm looking for a way to proportionally fill a cell based on the contents.
    > For example, if the contents of four rows are:
    >
    > .8
    > .75
    > .5
    > 1
    >
    > then the contents of these cells are 4/5, 3/4, 1/2, and completely
    > full....(either top to bottom or left to right).
    >
    > Right now I'm using a stacked bar graph, but lining up the axes of the graph
    > on top of the the cell gridlines is quite a challenge and very time
    > consuming. I'm not optimistic, but thought I would put this question out
    > here to check.
    >
    > Many thanks in advance.
    >
    > MJohn


  5. #5
    M John
    Guest

    RE: proportionally fill a cell

    The macro code supplied by Mr. Williams does exactly what I need....and I
    modified slightly to be:

    Sub Proportionally_Fill()

    Dim c As Range
    Dim v As Single
    Dim s As Shape

    For Each c In Selection
    If c.Value >= 0 And c.Value <= 1 Then

    v = c.Value
    Set s = ActiveSheet.Shapes.AddShape(msoShapeRectangle, _
    c.Left, c.Top, c.Width * v, c.Height)
    s.Fill.Visible = msoTrue
    s.Fill.ForeColor.SchemeColor = 17
    s.Line.Visible = msoFalse
    Selection.Font.ColorIndex = 2
    End If
    Next c
    End Sub

    Which allows me to select any number of cells and fill those cells
    propotionally (left to right) based on the values in those cells.

    Thanks for the quick reply.

    MJohn

    "Tom Ogilvy" wrote:

    > You want to select some quantity of cells and fill them with different colors
    > to make them look like a stacked bar graph based on the values in some other
    > cells?
    >
    > How many cells?
    >
    > Do you mean 4 cells and you want to adjust the height or width?
    >
    > Anyway, more information might garner a different response.
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "M John" wrote:
    >
    > > Is this possible:
    > >
    > > I'm looking for a way to proportionally fill a cell based on the contents.
    > > For example, if the contents of four rows are:
    > >
    > > .8
    > > .75
    > > .5
    > > 1
    > >
    > > then the contents of these cells are 4/5, 3/4, 1/2, and completely
    > > full....(either top to bottom or left to right).
    > >
    > > Right now I'm using a stacked bar graph, but lining up the axes of the graph
    > > on top of the the cell gridlines is quite a challenge and very time
    > > consuming. I'm not optimistic, but thought I would put this question out
    > > here to check.
    > >
    > > Many thanks in advance.
    > >
    > > MJohn


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1