Is there a way to display a progress bar in adjacent cells? For example:
Budget Column $5000, Expense Column $2500, Next column is a bar that shows
50% has been spent.

Thanks

Hi MeTheITGuy,

Easy if you have Excel 2007. Otherwise you could try this macro to build
your own progress bars.

Option Explicit

Sub BuildBar()
' Builds rectangle shapes in column C

' for this sample we assume that the range A2:B6
' contains budget in col A, and actual in col B
' we want a bar in Col C that is proportional to
' col B / col A

Dim inpRange As Range
Dim actual As Range
Dim barLength As Double

' you could use an input box here to ask the user for a range
' or you could define a dynamic named range and use that
' leave it up to you
Set inpRange = Range("A2:B6")

' clean up any previously built rectangles
CleanUp

For Each actual In inpRange.Columns(1).Cells
barLength = actual.Offset(0, 1).Value / actual.Value
Call AddRectangle(actual.Offset(0, 2), barLength)
Next actual

End Sub

Sub AddRectangle(dest As Range, barLength As Double)
' Adds a rectangle shape to fill the specified cell

Dim cL, cT, cW, cH As Single
Dim shp As Shape

With dest
cL = .Left
cT = .Top
cW = .Width
cH = .Height
End With

Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, cL, cT, cW, cH)

With shp
' name the shapes so that we can keep track of them
.Name = "Rect" & dest.Address
' set a fill colour
.Fill.ForeColor.SchemeColor = 10
' size them to be proportional to barLength
.ScaleWidth barLength, msoFalse, msoScaleFromTopLeft
End With

End Sub

Sub CleanUp()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 4) = "Rect" Then
shp.Delete
End If
Next shp
End Sub

Ed Ferrero

"MeTheITGuy" wrote in message
...
Is there a way to display a progress bar in adjacent cells? For example:
Budget Column $5000, Expense Column $2500, Next column is a bar that shows
50% has been spent.

Thanks

I do have office 2007. But didn't see anything in the help system on how to
do it.

"Ed Ferrero" wrote:

Hi MeTheITGuy,

Easy if you have Excel 2007. Otherwise you could try this macro to build
your own progress bars.

Option Explicit

Sub BuildBar()
' Builds rectangle shapes in column C

' for this sample we assume that the range A2:B6
' contains budget in col A, and actual in col B
' we want a bar in Col C that is proportional to
' col B / col A

Dim inpRange As Range
Dim actual As Range
Dim barLength As Double

' you could use an input box here to ask the user for a range
' or you could define a dynamic named range and use that
' leave it up to you
Set inpRange = Range("A2:B6")

' clean up any previously built rectangles
CleanUp

For Each actual In inpRange.Columns(1).Cells
barLength = actual.Offset(0, 1).Value / actual.Value
Call AddRectangle(actual.Offset(0, 2), barLength)
Next actual

End Sub

Sub AddRectangle(dest As Range, barLength As Double)
' Adds a rectangle shape to fill the specified cell

Dim cL, cT, cW, cH As Single
Dim shp As Shape

With dest
cL = .Left
cT = .Top
cW = .Width
cH = .Height
End With

Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, cL, cT, cW, cH)

With shp
' name the shapes so that we can keep track of them
.Name = "Rect" & dest.Address
' set a fill colour
.Fill.ForeColor.SchemeColor = 10
' size them to be proportional to barLength
.ScaleWidth barLength, msoFalse, msoScaleFromTopLeft
End With

End Sub

Sub CleanUp()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 4) = "Rect" Then
shp.Delete
End If
Next shp
End Sub

Ed Ferrero

"MeTheITGuy" wrote in message
...
Is there a way to display a progress bar in adjacent cells? For example:
Budget Column $5000, Expense Column $2500, Next column is a bar that shows
50% has been spent.

Thanks

In Excel 2007, enter the formula =B2/A2 where B2 contains budget and A2
contains actual.

Then, in the Home ribbon, select Conditional Formatting, Data Bars, click on
More Rules...
Check the 'Show Bar Only' option.

Copy down.

Ed Ferrero


"MeTheITGuy" wrote in message
...
I do have office 2007. But didn't see anything in the help system on how to
do it.

"Ed Ferrero" wrote:

Hi MeTheITGuy,

Easy if you have Excel 2007. Otherwise you could try this macro to build
your own progress bars.

Option Explicit

Sub BuildBar()
' Builds rectangle shapes in column C

' for this sample we assume that the range A2:B6
' contains budget in col A, and actual in col B
' we want a bar in Col C that is proportional to
' col B / col A

Dim inpRange As Range
Dim actual As Range
Dim barLength As Double

' you could use an input box here to ask the user for a range
' or you could define a dynamic named range and use that
' leave it up to you
Set inpRange = Range("A2:B6")

' clean up any previously built rectangles
CleanUp

For Each actual In inpRange.Columns(1).Cells
barLength = actual.Offset(0, 1).Value / actual.Value
Call AddRectangle(actual.Offset(0, 2), barLength)
Next actual

End Sub

Sub AddRectangle(dest As Range, barLength As Double)
' Adds a rectangle shape to fill the specified cell

Dim cL, cT, cW, cH As Single
Dim shp As Shape

With dest
cL = .Left
cT = .Top
cW = .Width
cH = .Height
End With

Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, cL, cT, cW, cH)

With shp
' name the shapes so that we can keep track of them
.Name = "Rect" & dest.Address
' set a fill colour
.Fill.ForeColor.SchemeColor = 10
' size them to be proportional to barLength
.ScaleWidth barLength, msoFalse, msoScaleFromTopLeft
End With

End Sub

Sub CleanUp()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 4) = "Rect" Then
shp.Delete
End If
Next shp
End Sub

Ed Ferrero

"MeTheITGuy" wrote in message
...
Is there a way to display a progress bar in adjacent cells? For
example:
Budget Column $5000, Expense Column $2500, Next column is a bar that
shows
50% has been spent.

Thanks

So I've been playing with this functionality a bit, and it seems a bit
finicky. For example an item that is only 12% complete will be graphically
depicted as 50%. And something that is less than 50% shows up as more like
75%. So I mess with it and get it to accurately depict the percentage, but as
soon as I copy that formatting to the cells below, it changes back.

What I've done is put the percentage in the column, then have the percentage
text show up along with the bar. I just go to conditional formatting data
bars blue and the first item shows up fine. But as soon as I drag that
formatting down, the bar lenght gets messed up.

"Ed Ferrero" wrote:

In Excel 2007, enter the formula =B2/A2 where B2 contains budget and A2
contains actual.

Then, in the Home ribbon, select Conditional Formatting, Data Bars, click on
More Rules...
Check the 'Show Bar Only' option.

Copy down.

Ed Ferrero


"MeTheITGuy" wrote in message
...
I do have office 2007. But didn't see anything in the help system on how to
do it.

"Ed Ferrero" wrote:

Hi MeTheITGuy,

Easy if you have Excel 2007. Otherwise you could try this macro to build
your own progress bars.

Option Explicit

Sub BuildBar()
' Builds rectangle shapes in column C

' for this sample we assume that the range A2:B6
' contains budget in col A, and actual in col B
' we want a bar in Col C that is proportional to
' col B / col A

Dim inpRange As Range
Dim actual As Range
Dim barLength As Double

' you could use an input box here to ask the user for a range
' or you could define a dynamic named range and use that
' leave it up to you
Set inpRange = Range("A2:B6")

' clean up any previously built rectangles
CleanUp

For Each actual In inpRange.Columns(1).Cells
barLength = actual.Offset(0, 1).Value / actual.Value
Call AddRectangle(actual.Offset(0, 2), barLength)
Next actual

End Sub

Sub AddRectangle(dest As Range, barLength As Double)
' Adds a rectangle shape to fill the specified cell

Dim cL, cT, cW, cH As Single
Dim shp As Shape

With dest
cL = .Left
cT = .Top
cW = .Width
cH = .Height
End With

Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, cL, cT, cW, cH)

With shp
' name the shapes so that we can keep track of them
.Name = "Rect" & dest.Address
' set a fill colour
.Fill.ForeColor.SchemeColor = 10
' size them to be proportional to barLength
.ScaleWidth barLength, msoFalse, msoScaleFromTopLeft
End With

End Sub

Sub CleanUp()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 4) = "Rect" Then
shp.Delete
End If
Next shp
End Sub

Ed Ferrero

"MeTheITGuy" wrote in message
...
Is there a way to display a progress bar in adjacent cells? For
example:
Budget Column $5000, Expense Column $2500, Next column is a bar that
shows
50% has been spent.

Thanks

Are you using the format painter to copy formatting down? Does not seem to
'mess up' for me.

You could also select the whole range and set conditional formatting for all
the cells at the one time.

Ed Ferrero

So I've been playing with this functionality a bit, and it seems a bit
finicky. For example an item that is only 12% complete will be
graphically
depicted as 50%. And something that is less than 50% shows up as more like
75%. So I mess with it and get it to accurately depict the percentage, but
as
soon as I copy that formatting to the cells below, it changes back.

What I've done is put the percentage in the column, then have the
percentage
text show up along with the bar. I just go to conditional formatting
data
bars blue and the first item shows up fine. But as soon as I drag that
formatting down, the bar lenght gets messed up.

"Ed Ferrero" wrote:

In Excel 2007, enter the formula =B2/A2 where B2 contains budget and A2
contains actual.

Then, in the Home ribbon, select Conditional Formatting, Data Bars, click
on
More Rules...
Check the 'Show Bar Only' option.

Copy down.

Ed Ferrero


"MeTheITGuy" wrote in message
...
I do have office 2007. But didn't see anything in the help system on how
to
do it.

"Ed Ferrero" wrote:

Hi MeTheITGuy,

Easy if you have Excel 2007. Otherwise you could try this macro to
build
your own progress bars.

Option Explicit

Sub BuildBar()
' Builds rectangle shapes in column C

' for this sample we assume that the range A2:B6
' contains budget in col A, and actual in col B
' we want a bar in Col C that is proportional to
' col B / col A

Dim inpRange As Range
Dim actual As Range
Dim barLength As Double

' you could use an input box here to ask the user for a range
' or you could define a dynamic named range and use that
' leave it up to you
Set inpRange = Range("A2:B6")

' clean up any previously built rectangles
CleanUp

For Each actual In inpRange.Columns(1).Cells
barLength = actual.Offset(0, 1).Value / actual.Value
Call AddRectangle(actual.Offset(0, 2), barLength)
Next actual

End Sub

Sub AddRectangle(dest As Range, barLength As Double)
' Adds a rectangle shape to fill the specified cell

Dim cL, cT, cW, cH As Single
Dim shp As Shape

With dest
cL = .Left
cT = .Top
cW = .Width
cH = .Height
End With

Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, cL, cT, cW,
cH)

With shp
' name the shapes so that we can keep track of them
.Name = "Rect" & dest.Address
' set a fill colour
.Fill.ForeColor.SchemeColor = 10
' size them to be proportional to barLength
.ScaleWidth barLength, msoFalse, msoScaleFromTopLeft
End With

End Sub

Sub CleanUp()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 4) = "Rect" Then
shp.Delete
End If
Next shp
End Sub

Ed Ferrero

"MeTheITGuy" wrote in message
...
Is there a way to display a progress bar in adjacent cells? For
example:
Budget Column $5000, Expense Column $2500, Next column is a bar that
shows
50% has been spent.

Thanks