Value and Status in charts

guntoter

Board Regular
Joined
Nov 8, 2002
Messages
65
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?
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Corni

Active Member
Joined
Mar 2, 2002
Messages
328
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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

Board Regular
Joined
Nov 8, 2002
Messages
65
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,312
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top