Stacked Bar Chart with Dynamic Formatting


New Member
Mar 5, 2010
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.

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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.

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)))
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)
With co.Chart.SeriesCollection(4).Points(col)       ' create a label
    .DataLabel.Text = "Current"
    .DataLabel.Font.Size = 14
    .DataLabel.Font.Color = RGB(5, 200, 5)
End With
End Sub
Upvote 0

Forum statistics

Latest member

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
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 "".
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