Stacked Bar Chart with Dynamic Formatting

jsweeney788

New Member
Joined
Mar 5, 2010
Messages
12
I have a stacked bar chart with 20 columns and the data has been sorted so that the graph is shown in ascending order. Certain formats need to be applied, and I am trying to determine if this can be automated, either through a macro or property within the graph.

The requirements for the graph are as follows:

  1. Each column pertains to a different entity, but I can only show a label for the entity/column pertaining to the report. All other columns are simply benchmarks and cannot be labeled.
  2. Each portion of the stacked column needs to be a different color, but the color can be the same for all entities.
  3. The column pertaining to the entity that is being reported on needs to be highlighted somehow– it can either be color coded by using different colors than the ones used for all other entities or it can be labelled with the entity name.

I have manually set up the graph according to the specifications above, utilizing both approached outlined in #3. However, I am encountering issues with both. When color coding, the colors no longer point to the correct entity when the data is gathered for another reporting period and is resorted to display the graph in ascending order. When I simply add a text box label over the column representing the entity in question, and keep the colors for all columns the same, I have to manually move the text box when the data is resorted. I realize this could all be avoided by not sorting the data, but sorting is the only way that the graph presents the data clearly.

The manual formatting steps are not cumbersome for one graph, but having to manually update colors or text boxes for twenty graphs is a bit more tedious. My goal is to have the graphs automatically update and highlight the correct column in one way or another, but I am not sure if this is even possible.

Any ideas or suggestions would be greatly appreciated. I can also provide further clarification, if necessary.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,129
Hello
See if this example is useful:

  • It uses 4 series and 8 columns
  • The user is prompted for a column number; the special format will move to this column.
onecol.JPG

Code:
Option Base 1


Sub Sweeney()
Dim co As ChartObject, i%, col%, p As Point, c
c = Array(95, 15, 85, 180, 25, 160, 230, 70, 210, 240, 160, 230) ' rgb components
Set co = Worksheets("Sheet1").ChartObjects("Chart7")
On Error Resume Next
co.Chart.SeriesCollection(4).DataLabels.Delete      ' remove old label
On Error GoTo 0
col = Application.InputBox("Enter a column number:", _
co.Chart.SeriesCollection(1).Points.Count & " columns", , , , , , 1)
For i = 1 To 4                                      ' reset colors
    For Each p In co.Chart.SeriesCollection(i).Points
        p.Format.Fill.ForeColor.RGB = _
        RGB(c(1 + 3 * (i - 1)), c(2 + 3 * (i - 1)), c(3 + 3 * (i - 1)))
    Next
Next
For i = 1 To 4                                      ' apply colors for chosen column
    co.Chart.SeriesCollection(i).Points(col).Format.Fill.ForeColor.RGB = _
    RGB(60 * i, 55 * i, 250 - 40 * i)
Next
With co.Chart.SeriesCollection(4).Points(col)       ' create a label
    .ApplyDataLabels
    .DataLabel.Text = "Current"
    .DataLabel.Font.Size = 14
    .DataLabel.Font.Color = RGB(5, 200, 5)
End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,128,136
Messages
5,628,911
Members
416,352
Latest member
Lunox01

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