Zolteg Posted February 4, 2011 Share Posted February 4, 2011 G'day. I'm working with an Excel chart, and need to add two static lines on a chart at 80% and 90% value of a given figure. To do this, I'm using a method described at http://peltiertech.com/Excel/Charts/AddLineHorzErrBar.htmlI'm running into a couple of problems with first pasting the data into the chart, turning the data point into an errorbar, and then colouring the errorbar.I can work around the first two by kludging in Send commands, however for the life of me I cannot find how to change the colour of the error bar.As background, the relevant excel macro would be:expandcollapse popupSub Macro1() ' Sub Macro2() ' ' Macro2 Macro ' Macro recorded 4/02/2011 ' ' Sheets("Sheet1").Select Range("D13:E13").Select Selection.Copy Sheets("Chart1").Select ActiveChart.PlotArea.Select ActiveChart.SeriesCollection.Paste Rowcol:=xlRows, SeriesLabels:=True, _ CategoryLabels:=False, Replace:=False, NewSeries:=True ActiveChart.SeriesCollection(4).Select Application.CutCopyMode = False With Selection.Border .ColorIndex = 6 .Weight = xlThin .LineStyle = xlContinuous End With With Selection .MarkerBackgroundColorIndex = xlNone .MarkerForegroundColorIndex = xlNone .MarkerStyle = xlNone .Smooth = True .MarkerSize = 3 .Shadow = False End With ActiveChart.SeriesCollection(4).ErrorBar Direction:=xlX, Include:= _ xlPlusValues, Type:=xlFixedValue, Amount:=31 ActiveChart.SeriesCollection(4).ErrorBars.Select With Selection.Border .LineStyle = xlContinuous .ColorIndex = 6 .Weight = xlMedium End With Selection.EndStyle = xlCap End SubFor the life of me I simply cannot get my script to select the errorbar created by my kludge, using the code below.$oExcel.ActiveChart.SeriesCollection(3).ErrorBars.Select With $oexcel.Selection.Border .LineStyle = $xlContinuous .ColorIndex = 6 .Weight = $xlMedium EndWith $oExcel.Selection.EndStyle = $xlCap $oExcel.ActiveChart.SeriesCollection(4).ErrorBars.Select With $oExcel.Selection.Border .LineStyle = $xlContinuous .ColorIndex = 46 .Weight = $xlMedium EndWith $oExcel.Selection.EndStyle = $xlCapAny pointers/tips/recodes welcome. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now