Value and Status in charts

[guntoter]

I have 8 silos that contains tons of material. The silos not only have a ton value but also a status (such as Testing or Ship or Filling. How can I get the bar chart to display a the tons of each silo by the bars heigth, and also have each bar change color to display it's status?

 

[Corni]

By creating a bar chart the first part of the problem is resolved, the bars size will reflect the amount in each silo. For the second requirement, to have bar colour dynamically changed according to the status of the silo (T, S or F), first of all add an extra column with the status of the silo (range C1:C8) and then add a Worksheet_Change procedure with the following code:

Application.ScreenUpdating = False
Application.EnableEvents = False

If Application.Intersect(Target, Range("C1:C8")) Is Nothing Then
Exit Sub
End If

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(Target.Rows.Row).Select
Select Case Cells(Target.Rows.Row, 3).Value
Case "T"
Selection.Interior.ColorIndex = 3
Case "S"
Selection.Interior.ColorIndex = 4
Case "F"
Selection.Interior.ColorIndex = 5
End Select
ActiveWindow.Visible = False
Windows("Silos Chart.xls").Activate
Range("A1").Select

Application.ScreenUpdating = True
Application.EnableEvents = True

Any time the silo status changes, the colour fo the relevant bar does the same.
This message was edited by Corni on 2002-11-09 23:24

 

[Yogi Anand]

Hi guntoter:

Welcome to the Board!

As far as showing the tonnage value in the column bar chart for each silo, you can go to Chart Options -- then Data Labels -- then select Values.

I believe, in the second part of your question, you are asking whether, the color of the bar can automatically change, as the tonnage in the silo changes -- I don't know abou this one. You may also want to clarify your intent on this part.

 

[tusharm]

On 2002-11-09 21:03, guntoter wrote:
I have 8 silos that contains tons of material. The silos not only have a ton value but also a status (such as Testing or Ship or Filling. How can I get the bar chart to display a the tons of each silo by the bars heigth, and also have each bar change color to display it's status?

Check Jon Peltier's pages on conditional formatting of a chart (http://www.geocities.com/jonpeltier/Excel/Charts/index.html#CondChart). The example he uses is a column (vertical bar) chart.

 

[guntoter]

Thank all of you who responded to my problem. I think I am going to try the VB code solution that corni sent. I know some VB but that code is way over my head, so thanks for it. I never would have got that one on my own. That chart site tusharm sent was interesting, and I added it to my favorite list.

Thanks again,
guntoter