Stacked Bar Chart with Dynamic Formatting

[jsweeney788]

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.

 

[Worf]

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.

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