Excel Masters Please Help!

  • Thread starter Thread starter gus_scottexley
  • Start date Start date
G

gus_scottexley

I was wondering, is it possible to base the amount of fill in a cell on
a percentage? like a meter kind of thing, i.e. if it's 50% then the
fill is only half way (from left to the middle) and if it's 80% then
it's 8/10ths of the way across, get the idea?

Is it possible??

Many thanks in advance.

Gus
 
It's not possible to fill a cell partially, but you can fake it. One
way:

Put a textbox from the drawing toolbar on the sheet. name it
"FillBox".

Assume B1 contains your percent calculation and is the cell you want
to fill. If you don't want numbers to show, choose
Format/Cells/Number/Custom and enter ";;;" (without quotes) in the
textbox.

Now put this in the Worksheet code module (right-click on the
sheet's tab and choose View Code):

Private Sub Worksheet_Calculate()
Dim tBox As Shape
Dim wide As Double

Set tBox = ActiveSheet.Shapes("FillBox")
With Range("B1")
wide = .Width
tBox.Top = .Top
tBox.Left = .Left
tBox.Height = .Height
tBox.Width = Application.Max(Application.Min( _
.Value * wide, wide), 0)
End With
With tBox.Fill
.ForeColor.SchemeColor = 3
.Visible = msoTrue
End With
End Sub
 
Another option is to shade the cell, based on the percent:

If the percentage is the result of a formula, you could use the REPT
function to fill the cell. For example, with A2: 100 and B2: 35 --

1. To set the maximum width, in cell C1, enter the following formula:
=REPT("|",50)
2. Adjust the column width to fit the resulting bar
3. In cell C2, enter the following formula:
=REPT("|",B2/A2*50)
The bar will fill approx. 35% of the cell.
 
Back
Top