Conditionally Format Chart Backgrounds

Recently, Paul, a reader, of the Chandoo Blog Post: Colors-in-excel-chart-labels-trick asked a question:

“Hi Chandoo,

Is it possible to change the background label color on chart depending on the value? ”

I answered with a general “Yes” and offered two solutions
1. Using CF to color the background cells behind the chart
2. A VBA Solution to change the chart colors

This post will examine how to implement each method:

I have attached sample files which includes both examples Excel 2007-13 Sample or Excel 97-2003 Sample
You can follow along in this file before attempting it on your own data.

Using Conditional Formatting to Color the background cells

In the sample file goto the CF Technique worksheet

In CF Technique worksheet you will see a set of data with dates and Scores for each date

Below the main table is a calculation of the slope of the line of best fit through the data
This shows either a positive number when the data is trending upwards or a negative number when it is trending downwards

For the purpose of this we can simply change the yellow cell C13 from 90 to 10 to change the slope from a Positive to a Negative value

Next to the chart is a simple X-Y Chart showing the Scores vs the Date (Blue) and a Line of best fit (Dashed Red)

The chart is exactly covering the range E3:L15, this is achieved by placing the chart roughly in position and then holding the Alt Button whilst dragging the corners or edges of the chart.

Note: The use of Alt forces Excel to Snap the object onto the cell edges and lock it there, so that when the column width or row height changes, the Chart will resize with it.

Next we set the colors of the Chart Area and Plot Area to Transparent (No Color)

The Chart area is the Background area of the chart, White in the following example

The Chart area is the Background area of the chart, Yellow in the following example

We can see that the chart area has no color in the above picture as we can see the Grid Lines through the Chart Area.

Format the Chart Area

Select the Chart

Right Click in the Chart Area,

Format Chart Area

Click on the Fill Tab and set the Fill to No Fill

Format the Plot Area

With the chart selected, Right Click in the Plot Area,
Format Plot Area
Click on the Fill Tab and set the Fill to No Fill

Click outside the chart

Apply a Conditional Formatting to the Range behind the Chart

Select the range E3:L15 (You won’t be able to use a mouse) or drag the chart out of the way first.

Goto Conditional Formatting Tab
New Rule
Use a formula to determine which cells to format
Enter the formula: =$C$15>0
Select the Format Button and select a Light Redish Color
Ok

Goto Conditional Formatting Tab
New Rule
Use a formula to determine which cells to format
Enter the formula: =$C$15<=0
Select the Format Button and select a Light Greenish Color
Ok

Now change the value of C15 from 90 to 10

The chart should change as per the below image:

Advantages:

• Doesn’t require VBA (VBA not permitted on some corporate systems)
• Simple to setup for those unfamiliar with VBA

Disadvantages:

• The Chart is locked to the cells and can’t be moved moved independently of the background cells
• More difficult to implement multiple color scenarios
• Harder to permit independent changes to the Chart and Plot areas

Using VBA to directly change the color of the Chart Chart Area

In the sample file goto the VBA Technique worksheet

You will see the same set of data with dates and Scores for each date

Select the Chart and notice that the Chart is called “Chart 1”

Goto VBA, Press Alt+F11

Double click on the VBA Technique code module

Copy and paste the following code into the module

Private Sub Worksheet_Calculate()

Dim myColor As Long
Dim myChart As String

Application.EnableEvents = False

If ActiveSheet.Name <> "VBA Technique" Then Exit Sub

myChart = "Chart 1"

If [c15] <> [OldSlope] Then

  If [c15] > 0 Then
    myColor = RGB(250, 190, 145) 'Apricot
  Else
    myColor = RGB(135, 235, 145) 'Pale Green
  End If
  
  ActiveSheet.ChartObjects(myChart).Activate
  ' Color the Chart Area
  With ActiveSheet.Shapes(myChart).Fill
    .Visible = msoTrue
    .ForeColor.RGB = myColor
    .Transparency = 0
    .Solid
  End With
  
  ' Color the Plot Area
  ActiveChart.PlotArea.Select
  With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = myColor
    .Transparency = 0
    .Solid
  End With
  
  ActiveWorkbook.Names.Add Name:="OldSlope", RefersToR1C1:="=" + CStr(Cells(15, 3).Value)
End If

Application.EnableEvents = True
Range("C17").Select

End Sub

Return to the Excel worksheet

Now change the value of C15 from 90 to 10

If the Chart area doesn’t change color follow the following few steps

Goto VBA (Alt+F11)
Open the Immediate window (Ctrl+G)
Type in Application.EnableEvents = True press enter
Go back to Excel (Alt+F11)

Advantages:

• Allows the Chart to be moved independently of the background cells
• Allows a much simpler implementation of multiple color scenarios
• Allows independent changes to the Chart and Plot areas as well as other Chart Elements

Disadvantages:

• Requires VBA (not permitted on some corporate systems)

Other Chart Conditional Formatting Posts

You may also be interested in the following Chart Formatting posts:

http://chandoo.org/wp/2011/08/19/selective-chart-axis-formating/

http://chandoo.org/wp/2011/08/22/custom-chart-axis-formating-part-2/

Closing

What do you think of these techniques?

Let us know in the comments below.