Conditionally Format Chart Backgrounds

Posted on March 27th, 2015 in Charts and Graphs , Excel Howtos , Huis , Posts by Hui , VBA Macros - 17 comments

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

CFCBG01

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

CFCBG02

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

CFCBG05

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,

CFCBG03

Format Chart Area

CFCBG04
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

CFCBG06

Now change the value of C15 from 90 to 10

The chart should change as per the below image:

CBG01

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”

CFCBG07

Goto VBA, Press Alt+F11

Double click on the VBA Technique code module

CFCBG08

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

CBG02

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.

Written by Hui...
Tags: , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

17 Responses to “Conditionally Format Chart Backgrounds”

  1. Khalid NGO says:

    Hi Hui,
    Very good post Sir, I had never seen Charts with CF before.
    Thank you...

    1 question:
    I have download the file, I am using 2007, and the chart on "VBA Technique" is not changing color while updating the values of Col C.
    I am not getting what I am doing wrong.

    Regards,

    • Hui... says:

      @Khalid

      A few ideas

      Save it to a trusted location and re-open it
      Enable macros
      It should then be ok

      If it still doesn't work goto VBA (Alt+F11)
      Open the Immediate window Ctrl+G
      type in Application.EnableEvents = True press enter
      Go back to Excel (Alt+F11)
      Change the Cell C13 again

      Let me know if it still isn't working

  2. Khalid NGO says:

    Oh great....
    It worked after i followed your instruction for immediate window..
    Many thanks Sir.

  3. […] he became Chandoo. I was going through emails before a good night sleep that I read his latest article and went through the technique he employed. He discussed two possibilities and immediately the […]

  4. […] shows two ways to change the background color on a chart, depending on a cell value. One method used VBA and the other uses conditional […]

  5. Jayant says:

    Excellent, also i need some guide lines to prepare dynamic chart like this one http://www.eia.gov/forecasts/steo/realprices/ , check on the range selectors on the scroll bar. i need to to keep my historical data ( past 8 years) summarized and this way it would be helpful.

    • Hui... says:

      @Jayant
      Excel 2013 includes a Date Selector which allows just this as a slicer

      Prior to 2013 you could do this with two controls
      one to set the start time and a second to set the duration of the window

      For more specific help please ask a question at the Chandoo.org Forums
      http://chandoo.org/forum/
      Please attach a sample file to allow a more specific answer.

  6. Andy F says:

    There is a workaround to the disadvantage of the formatting not moving with a dragged chart in the non-VBA method:

    1. Conditionally format a range out of the display area
    2. Take a snapshot of the conditionally formatted range
    3. Group the snapshot output with the chart
    4. Send the snapshot to the back of the group

    • Lasse says:

      I was about to suggest copying the ranges and inserting it as a linked image- that way it is resizable -to a point- and acts as one object with the conditional formatting -when moving, resizing, etc.

  7. devi says:

    hi
    i did one macro with in that particular one sheet only i want to set
    .DisplayFullScreen=ture but i was affected all the excel workbook
    i want to set this option to particular workbook and worksheet only not all the others

  8. devi says:

    pls help us to slove this problem its urgent

    • Hui... says:

      @Devi
      You will need to add a line to a Workbook close event like:
      Activeworkbook.DisplayFullScreen=False

      If you want it to apply to other worksheets in the same workbook
      add a similar line to each WorksheetActivate event
      ActiveWorksheet.DisplayFullScreen=False

      I am not on a pc and so can't try the actual code or event names

  9. […] How to do Conditional Formatting in the Chart […]

  10. CharlesHarris says:

    Hi

    On chart at top of this page there is a chart with plot area of 5 rows. Want to conditionally format each row based on a number in the source sheet it self.

    Hop to modify above code.

    Thanks

    Charles

Leave a Reply