Excel 2007 Chart colorindex?
in [Programming]
|
From: Jay on 29 Nov 2009 11:39 I have a macro that changes colors in a bar chart. The following code worked in Excel XP. Sheets("Chart").SeriesCollection(1).Interior.ColorIndex = 5 This command crashes Excel 2007, and I've noticed that the ColorIndex property does not ever appear to be a property of the SeriesCollection object in Excel 2007. Does anyone know how to control chart colors in Excel 2007?
From: Bernard Liengme on 29 Nov 2009 12:29 Here is a macro I recorded in XL 2010 beta . It works in XL2007 with the ..ForeColor.Brightness statement commented out (or deleted) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme Sub Macro1() ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Select With Selection.Format.Fill .Visible = msoTrue .ForeColor.ObjectThemeColor = msoThemeColorAccent3 ' this sets the colour .ForeColor.TintAndShade = 0 ' .ForeColor.Brightness = 0 ' comment this out .Transparency = 0 .Solid End With End Sub "Jay" <Jay(a)discussions.microsoft.com> wrote in message news:017231D3-A7E4-434B-82BF-4EDBD971FA48(a)microsoft.com... >I have a macro that changes colors in a bar chart. The following code >worked > in Excel XP. > > Sheets("Chart").SeriesCollection(1).Interior.ColorIndex = 5 > > This command crashes Excel 2007, and I've noticed that the ColorIndex > property does not ever appear to be a property of the SeriesCollection > object > in Excel 2007. > > Does anyone know how to control chart colors in Excel 2007? > >
From: Peter T on 29 Nov 2009 13:05 That code should work fine in Excel 2007, and ColorIndex is indeed a property of Series.Interior (but not simply SeriesCollection) Although for legacy it works, in 2007 though the preferred way is along the lines Bernard suggested, or say sr.Format.Fill.ForeColor.ObjectThemeColor = xlThemeColorAccent1 or sr.Format.Fill.ForeColor.RGB = 123456 where sr refers to the Series. However don't do that in earlier versions, or if you do declare sr As Object ' not As series If Val(Application.version) >= 12 then Excel 2007 code Else pre Excel2007 code What is "Chart"? If a chart sheet maybe you mean "Chart1". If a chartobject on a sheet named "Chart" maybe you mean to do Sheets("Chart").ChartObjects(1).Chart.SeriesCollection(1).Interior.ColorIndex = 5 Either way, Excel 2007 shouldn't have crashed, an error at most! Regards, Peter T "Jay" <Jay(a)discussions.microsoft.com> wrote in message news:017231D3-A7E4-434B-82BF-4EDBD971FA48(a)microsoft.com... >I have a macro that changes colors in a bar chart. The following code >worked > in Excel XP. > > Sheets("Chart").SeriesCollection(1).Interior.ColorIndex = 5 > > This command crashes Excel 2007, and I've noticed that the ColorIndex > property does not ever appear to be a property of the SeriesCollection > object > in Excel 2007. > > Does anyone know how to control chart colors in Excel 2007? > >
From: L_P on 9 Dec 2009 10:54 I've got something similar under pre-2007, but rather than assigning an explicit color value to the bars in my graph, I'm taking the value from a specific cell. (This allows the user to configure the graph's appearance). This code works (or has under the pre-2007 versions I've tried it on): Code: -------------------- With <path>.SeriesCollection(SeriesName) .Interior.Pattern = Target.Interior.Pattern .Interior.PatternColorIndex = Target.Interior.PatternColorIndex .Interior.ColorIndex = Target.Interior.ColorIndex End With -------------------- Here, SeriesName holds the name of the data series being changed, and Target is the Range (from the Excel sheet) which holds the desired format. But this is not working under 2007. There appear to be 2 problems: 1) It doesn't want to accept SeriesName as a valid selector for SeriesCollection. When I replace the variable with the explicit string it works fine (e.g. replace the variable SeriesName with "Series1"), even when that explicit string is the exact value of the string variable. Is there some new flag to add? 2) The color doesn't change properly. Now, I assume this is because, under 2007, the graph's colors are being computed from different fields - RGB, ForeColor, etc. (As suggested by the examples above). How do I, under 2007, pick up the "fill" information out of the Target range (background color and pattern) and apply that to the Series in the graph? thanks, LP -- L_P ------------------------------------------------------------------------ L_P's Profile: 1286 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=157914 [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
From: Peter T on 9 Dec 2009 12:06
See comments in-line - "L_P" <L_P.42xpc2(a)thecodecage.com> wrote in message > > I've got something similar under pre-2007, but rather than assigning an > explicit color value to the bars in my graph, I'm taking the value from > a specific cell. (This allows the user to configure the graph's > appearance). > > This code works (or has under the pre-2007 versions I've tried it on): > > > Code: > -------------------- > > With <path>.SeriesCollection(SeriesName) > .Interior.Pattern = Target.Interior.Pattern > .Interior.PatternColorIndex = Target.Interior.PatternColorIndex > .Interior.ColorIndex = Target.Interior.ColorIndex > End With In all versions "Pattern" is probably going to be wrong. There are different 'sets' of patterns for cells and chart formats, with many more available for the latter. If you really want to copy the pattern you'll need to make a lookup table of what pattern to apply to the chart that's similar in appearence to the cell pattern. Bit of experimantation to make the table > > > Here, SeriesName holds the name of the data series being changed, and > Target is the Range (from the Excel sheet) which holds the desired > format. > > But this is not working under 2007. > > There appear to be 2 problems: > > > 1) It doesn't want to accept SeriesName as a valid selector for > SeriesCollection. When I replace the variable with the explicit string > it works fine (e.g. replace the variable SeriesName with "Series1"), > even when that explicit string is the exact value of the string > variable. Is there some new flag to add? I can only assume something simple your end is going wrong here. Absolutely no reason why the "text" works and yet the same in a string variable fails. > > 2) The color doesn't change properly. > > Now, I assume this is because, under 2007, the graph's colors are being > computed from different fields - RGB, ForeColor, etc. (As suggested by > the examples above). > > How do I, under 2007, pick up the "fill" information out of the Target > range (background color and pattern) and apply that to the Series in the > graph? Although the 56 colour palette works in 2007 for compatibility, better for what you're trying to do to use RGB colours, eg (sr refers to the series) sr.Format.Fill.ForeColor.RGB = cell.Interior.Color Before doing anything might want to ensure the series fill is solid, simply sr.Format.Fill.Solid Then if you want to apply a pattern (from the lookup table) p = cell.Interior.Pattern If p <> 1 Or p <> xlNone Or p <> xlAutomatic then get pattern-number from lookup using p then sr.Fill.Patterned pattern-number sr.Format.Fill.BackColor.RGB = cell.Interior.PatternColor Note in Excel 2007 the cell's apparent fill colour might be from a Table style of a conditional format - these will not be returned from the cell's RGB format. Such a colour can be obtained but a lot more work. Regards, Peter T |