Excel 2007 Chart formatting doesn't "stick" when filtered list changes. This worked in excel 2003.

My chart is referencing data in a filtered list.  When I change the list, my chart changes.  I have colored the data points in my chart not because of specific values by based on the ordering (first six items are blue, the next six are green, the next two black, the next two yellow, etc).  This functionality worked in excel 2007 but now when I change the filtered list, they coloring disappears.  I have also gone through all the different filtered items and change those colors, but when I switch the filters...they are gone.  I am potentially looking for a macro that will apply my colors to these data items as I believe from reading in other posts that this is a known excel issue.  Please help.  I have included a simple example of what I am talking about.  It works as it should, but in my live example which has more moving parts...it works then it doesn't.  It is inconsistent and I need to rely no a solution.
Simple-Example.xlsx
majervisAsked:
Who is Participating?
 
dlmilleCommented:
Make the following changes:

Sub updateChartColors()
Dim myChart As Chart
Dim MySeries As Series
Dim myXValuesRange As Range
Dim seriesIndex As Integer

   
    'Set myChart = ActiveSheet.ChartObjects("Chart 1").Chart ' this for embedded chart

    Set myChart = Sheets("Chart1") 'changed for chart as separate sheet
    Set myXValuesRange = Sheet1.Range("G10:R10") 'changed for chart as separate sheet

See attached:

Dave
Simple-Example-r2.xlsm
0
 
Rory ArchibaldCommented:
I'm not really sure how we are supposed to troubleshoot if you provide us with a sample that works rather than the one that doesn't?
0
 
majervisAuthor Commented:
I attached a sample of what works through the "front end".  Perhaps if you could come up with a macro that can format the data points.  What is frustrating about the situation is that it works sporadically.  I have many different iterations of the chart based on filtering I do on two variables.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
majervisAuthor Commented:
what actually happens is that I format the data points individually to color them correctly, will change the filter to a different item, color those, go back to the first version and the colors go back to the default.
0
 
Rory ArchibaldCommented:
Is your actual chart a pivot chart or a normal chart?
0
 
majervisAuthor Commented:
it is a normal chart.  I prefered to use charts that are based on filters which are in turn based on data in a pivot chart as the non-pivot version charts gives me more flexibility.
0
 
Rory ArchibaldCommented:
Then I can't think of any reason for the colours to change, since you are plotting the same actual range of cells each time - at least, based on the sample you posted.
0
 
majervisAuthor Commented:
I did some poking around and microsoft acknowledged this as an issue with at least excel 2003.  I am looking for perhaps a macro to do this instead so I can rely on it.
0
 
Rory ArchibaldCommented:
Can you point me in the direction of that?
0
 
majervisAuthor Commented:
I am still looking for the MSFT link that references the difficiency
0
 
majervisAuthor Commented:
http://support.microsoft.com/?id=215904

While my chart is not a pivot chart, the data is based off of information in a pivot table.  
0
 
Rory ArchibaldCommented:
I thought that might be what you were referring to - it is specifically a pivot chart issue and should not apply to a normal chart (the issue arises because pivot charts recreate every series when the data is refreshed, and hence revert to default formatting again). That issue was also related to 2003 and prior versions of Excel, whereas you did say this did not occur for you with 2003?
0
 
majervisAuthor Commented:
didn't happen with 2003, but is happening now with 2007, but thought it was relevant.
0
 
Rory ArchibaldCommented:
No, I don't believe so. The issue referred to in that article was one of the biggest bugbears users had with pivot charts before they rectified it in 2007. It's almost exactly the opposite of your issue. :)
If your setup is as you demonstrated in the example, hen I am totally at a loss, since your chart is plotting the same cells every time (even though they link to different data in the pivot table). The only way I could see you having a problem with that would be if you were using GetPivotData formulas rather than straight links, in which case you might have error values that would not plot on the chart.
0
 
majervisAuthor Commented:
One thing I just noticed is that I am in combatibility mode.  I wonder if this is impacting it?  Thanks for your help.
0
 
Rory ArchibaldCommented:
Again that seems unlikely since it doesn't happen in 2003 and you are using a 2003 format workbook.
I'm afraid that without seeing a workbook that actually demonstrates your problem, I don't think I can help you.
0
 
Rory ArchibaldCommented:
I will bring this to the attention of the Excel regulars and see if any of them have any other ideas.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

for what it's worth, the chart does not lose its formatting in Excel 2010, so I'm inclined to put this down to the buggy-ness of Excel 2007.

How fixed are you on using this format? If you could live with the month names as X axis labels, then you can create a different data layout and chart each quarter as a different series in a stacked column chart. The empty cells won't feature as data points and you can format each series with a different color. That should even stick in 2007. See attached.

Also, I've created a second chart from the changed data layout, following your original chart series pattern. Again, in 2010 the formatting stays when the filter changes, but I'd like to know how Excel 2007 treats that chart variation.

looking forward to feedback.

cheers, teylyn
Copy-of-Simple-Example.xlsx
0
 
dlmilleCommented:
Ok - per your request (and it follows some addin I'm working on, so I pulled out the relevant "stuff"), the attached is your worksheet, with the following modifications.

1.  The X-Range labels have a color fill which holds the "last stored" color for each of the series (a.k.a., from January -> December)
2.  There are two buttons on the worksheet - one to run the macro StoreColors() - which copies each series interior.color onto the X-Range Labels (hence, the settings are being stored); the other to run the macro updateChartColors() - which retrieves the colors from the X-Range Labels area and updates each series, accordingly.

See attached.  And here's the simple code:
Sub StoreColors()
Dim myChart As Chart
Dim MySeries As Series
Dim myXValuesRange As Range
Dim seriesIndex As Integer

    Set myChart = ActiveSheet.ChartObjects("Chart 1").Chart
    
    Set myXValuesRange = Range("G10:R10")
    
    seriesIndex = 0
    For Each MySeries In myChart.SeriesCollection
        myXValuesRange.Cells(1, 1).Offset(0, seriesIndex).Interior.Color = MySeries.Interior.Color
        seriesIndex = seriesIndex + 1
    Next MySeries
    
    
End Sub
Sub updateChartColors()
Dim myChart As Chart
Dim MySeries As Series
Dim myXValuesRange As Range
Dim seriesIndex As Integer

    Set myChart = ActiveSheet.ChartObjects("Chart 1").Chart
    
    Set myXValuesRange = Range("G10:R10")
    
    seriesIndex = 0
    For Each MySeries In myChart.SeriesCollection
        MySeries.Interior.Color = myXValuesRange.Cells(1, 1).Offset(0, seriesIndex).Interior.Color
        seriesIndex = seriesIndex + 1
    Next MySeries
    
End Sub

Open in new window


PS - I use Active-X buttons so this is for Excel 2007+.  Otherwise, change those to the older buttons and assign macros, accordingly, - or just run the macros from the Tools-Macro menu in Excel 2003...

Enjoy!

Dave
Simple-Example-r2.xlsm
0
 
dlmilleCommented:
Hopefully, the code is simple enough for you to adapt to your needs for other innovative "creations"

Dave
0
 
Rory ArchibaldCommented:
Dave,
What does Activex have to do with 2007 onward?
Rory
0
 
dlmilleCommented:
I could have mis "spoke", but I thought those controls don't work with Excel 2003, do they?

Dave
0
 
dlmilleCommented:
I did misspeak.  The active-x controls work just fine on Excel 2003, lol...

I started up a session and saved the file as Excel 97-2003 and tested and it works just fine in 2003 as well.

Thanks for the correction, rorya.

Dave
0
 
Rory ArchibaldCommented:
ActiveX is fine unless you are on a Mac! ;)
0
 
majervisAuthor Commented:
Thank you all for your feedback.  I will check out the VBA code to see if it works for me.  I have been out of town hence my silence.  Will get back soon.
0
 
majervisAuthor Commented:
dlmille:

Thank you for this.  How would I do this if the chart was in a separate sheet.  I should have created my example with this in mind.  Let's called the sheet the chart is on "quintile chart".  Thank you in advance.
0
 
majervisAuthor Commented:
any thoughts on how this would work on a chart on a separate workbook?  I am not savy in VBA.  I even tried using record macro to capture all of my chart formatting but it gives me an error message.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.