asked on

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

Rory Archibald

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?

majervis

ASKER

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.

majervis

ASKER

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.

Rory Archibald

Is your actual chart a pivot chart or a normal chart?

majervis

ASKER

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.

Rory Archibald

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.

majervis

ASKER

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.

Rory Archibald

Can you point me in the direction of that?

majervis

ASKER

I am still looking for the MSFT link that references the difficiency

majervis

ASKER

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.

Rory Archibald

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?

majervis

ASKER

didn't happen with 2003, but is happening now with 2007, but thought it was relevant.

Rory Archibald

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.

majervis

ASKER

One thing I just noticed is that I am in combatibility mode. I wonder if this is impacting it? Thanks for your help.

Rory Archibald

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.

Rory Archibald

I will bring this to the attention of the Excel regulars and see if any of them have any other ideas.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

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

dlmille

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

dlmille

Hopefully, the code is simple enough for you to adapt to your needs for other innovative "creations"

Dave

Rory Archibald

Dave,
What does Activex have to do with 2007 onward?
Rory

dlmille

I could have mis "spoke", but I thought those controls don't work with Excel 2003, do they?

Dave

dlmille

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

Rory Archibald

ActiveX is fine unless you are on a Mac! ;)

majervis

ASKER

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.

majervis

ASKER

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.

majervis

ASKER

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.

ASKER CERTIFIED SOLUTION

dlmille

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

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

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