Plot area banding

Banding Plot Area of Bubble chart

The normal techniques used to create banding colours in the plot area will not work for Bubble charts as they do are one of the chart styles that can not be combined.
To get around this problem we will use a second chart to handle the banding and use some VBA code to align the plot areas of both charts. Of course if you wanted you could do it manually.
First create a bubble chart in the normal way with 3 sets of data for X, Y and size.

You will need to set the Pattern of the Chart Area and Plot Area to be none. This will make the chart transparent when not selected.

Excel 2003	Excel 2007

The other thing will need to do is fix the Value axis. The reason for this is we want to align the bands on one chart with the values on another and if the axis is not fixed on the chart with the banding we will have a gap at the top of the plotarea.

Excel 2003	Excel 2007

We now need to create a clustered column chart for the banding, based on the data in F1:G4.
The values specify the height of each of the bands. Not that you will need to plot the data by rows in order to get 3 series rather than 1 series with 3 categories.

Then format the cluster column chart to have a Overlap of 100 and Gap width of 100. Once the Value axis is set to the same values as the bubble chart the columns will fill the whole of the plot area.
Change the colour of each of the bands to suit. I have gone with the traffic light Red, Yellow, Green.

Excel 2003	Excel 2007

To complete the banding chart you will need to remove Legend, any chart titles, Category axis labels and tickmarks. The same for the Value axis. You should end up with a chart that looks like this. Do not worry about the size or position of the chart or it's plot area. The VBA routine will deal with all of that.

The only thing left to do is identify each charts name so we can tell the routine which is the bubble chart and which is the banding one. For this post I have kept things relatively simple and not built a UI to allow selection of each chart.
In Excel 2003 the quickest way is to select the chart whilst holding the SHIFT key. This will select the chart as an object and display it's name in the Name Box, next to the formula bar. In the example workbook the bubble chart is called "Chart 8"

For Excel 2007 it is much easier as the charts name is displayed on the Layout tab within the Properties group.

Once you have the names of both charts you can enter them in the code.

Sub Test()

    With ActiveSheet
        AlignPlotAreas .ChartObjects("Chart 8").Chart, .ChartObjects("Chart 12").Chart
    End With

End Sub

This is the routine that positions the chart and plotarea.

Function AlignPlotAreas(TopChart As Chart, BottomChart As Chart)

    Dim lngPreviousValue As Long

    With TopChart.Parent
        BottomChart.Parent.Left = .Left
        BottomChart.Parent.Top = .Top
        BottomChart.Parent.Width = .Width
        BottomChart.Parent.Height = .Height
    End With

    ' reduce width/height so we can move area around without hitting edges
    BottomChart.PlotArea.Width = TopChart.PlotArea.Width * 0.5
    BottomChart.PlotArea.Height = TopChart.PlotArea.Height * 0.5

    BottomChart.PlotArea.Left = TopChart.PlotArea.Left
    lngPreviousValue = BottomChart.PlotArea.Left
    Do While BottomChart.PlotArea.InsideLeft < TopChart.PlotArea.InsideLeft
        BottomChart.PlotArea.Left = BottomChart.PlotArea.Left + 1
        If lngPreviousValue = BottomChart.PlotArea.Left Then
            Exit Do
        End If
        lngPreviousValue = BottomChart.PlotArea.Left
    Loop

    lngPreviousValue = BottomChart.PlotArea.Left
    Do While BottomChart.PlotArea.InsideLeft > TopChart.PlotArea.InsideLeft
        BottomChart.PlotArea.Left = BottomChart.PlotArea.Left - 1
        If lngPreviousValue = BottomChart.PlotArea.Left Then
            Exit Do
        End If
        lngPreviousValue = BottomChart.PlotArea.Left
    Loop

    BottomChart.PlotArea.Top = TopChart.PlotArea.Top
    lngPreviousValue = BottomChart.PlotArea.Top
    Do While BottomChart.PlotArea.InsideTop < TopChart.PlotArea.InsideTop
        BottomChart.PlotArea.Top = BottomChart.PlotArea.Top + 1
        If lngPreviousValue = BottomChart.PlotArea.Top Then
            Exit Do
        End If
        lngPreviousValue = BottomChart.PlotArea.Top
    Loop

    lngPreviousValue = BottomChart.PlotArea.Top
    Do While BottomChart.PlotArea.InsideTop > TopChart.PlotArea.InsideTop
        BottomChart.PlotArea.Top = BottomChart.PlotArea.Top - 1
        If lngPreviousValue = BottomChart.PlotArea.Top Then
            Exit Do
        End If
        lngPreviousValue = BottomChart.PlotArea.Top
    Loop

    BottomChart.PlotArea.Width = TopChart.PlotArea.Width
    lngPreviousValue = BottomChart.PlotArea.Width
    Do While BottomChart.PlotArea.InsideWidth < TopChart.PlotArea.InsideWidth
        BottomChart.PlotArea.Width = BottomChart.PlotArea.Width + 1
        If lngPreviousValue = BottomChart.PlotArea.Width Then
            Exit Do
        End If
        lngPreviousValue = BottomChart.PlotArea.Width
    Loop

    lngPreviousValue = BottomChart.PlotArea.Width
    Do While BottomChart.PlotArea.InsideWidth > TopChart.PlotArea.InsideWidth
        BottomChart.PlotArea.Width = BottomChart.PlotArea.Width - 1
        If lngPreviousValue = BottomChart.PlotArea.Width Then
            Exit Do
        End If
        lngPreviousValue = BottomChart.PlotArea.Width
    Loop

    BottomChart.PlotArea.Height = TopChart.PlotArea.Height
    lngPreviousValue = BottomChart.PlotArea.Height
    Do While BottomChart.PlotArea.InsideHeight < TopChart.PlotArea.InsideHeight
        BottomChart.PlotArea.Height = BottomChart.PlotArea.Height + 1
        If lngPreviousValue = BottomChart.PlotArea.Height Then
            Exit Do
        End If
        lngPreviousValue = BottomChart.PlotArea.Height
    Loop

    lngPreviousValue = BottomChart.PlotArea.Height
    Do While BottomChart.PlotArea.InsideHeight > TopChart.PlotArea.InsideHeight
        BottomChart.PlotArea.Height = BottomChart.PlotArea.Height - 1
        If lngPreviousValue = BottomChart.PlotArea.Height Then
            Exit Do
        End If
        lngPreviousValue = BottomChart.PlotArea.Height
    Loop

End Function