数据点标记（散点或线条）Excel VBA 的填充和边框颜色属性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22139904/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Fill and Border color property of data point marker (scatter or line) Excel VBA
提问by user3373638
I am writing some code to loop through each data point in a chart and change the color (fill/border) of the point marker if it is the wrong color. I don't want it to change if it is a compliant color (using a select case).
我正在编写一些代码来遍历图表中的每个数据点，如果颜色错误，则更改点标记的颜色（填充/边框）。如果它是合规的颜色（使用选择案例），我不希望它改变。
Problem: Checking markerbackgroundcolor and markerforegroundcolor returns "-1" no matter what the color is until i manually change the color (right click marker and change fill from automatic to solid).Something to do with Automatic colors.
问题：检查markerbackgroundcolor 和markerforegroundcolor 返回“-1”，无论颜色是什么，直到我手动更改颜色（右键单击标记并将填充从自动更改为纯色）。与自动颜色有关。
Basic example in immediate window (after inserted scatter chart without changing formatting):
即时窗口中的基本示例（插入散点图后不更改格式）：
?Activechart.SeriesCollection(1).points(1).markerbackgroundcolor
returns
-1
Then without doing anything else:
然后什么都不做：
Activechart.SeriesCollection(2).points(1).markerbackgroundcolor=255
?Activechart.SeriesCollection(2).points(1).markerbackgroundcolor
255
Is there another property of the marker I can test instead? Or another approach entirely? The checking of the color itself is critical to other parts of the code so all the work arounds I've tried so far are not cutting it : (
我可以测试标记的另一个属性吗？或者完全是另一种方法？检查颜色本身对代码的其他部分至关重要，因此到目前为止我尝试过的所有解决方法都没有削减它:(
PS new user, apologies if haven't formulated question too well.
PS新用户，如果没有很好地提出问题，请见谅。
回答by Sam
I don't think that you can change individual data points (or, at least I don't know how). I think that the problem lies in checking for "compliant colors." This type of color property seems to require a Long value which corresponds to a color.
我不认为您可以更改单个数据点（或者，至少我不知道如何更改）。我认为问题在于检查“合规颜色”。这种类型的颜色属性似乎需要一个与颜色相对应的 Long 值。
This code worked for me (Excel 2007) on a line-chart in chart I created for a project (I added the Debug.Printlines to show the numbers returned for Series in my chart)...
此代码在我为项目创建的图表中的折线图上对我有用（Excel 2007）（我添加了Debug.Print线条以显示图表中系列返回的数字）...
Sub formatTable()
Dim wb As Workbook
Dim ws As Worksheet
Dim co As ChartObject
Dim c As Integer
Set wb = ActiveWorkbook
Set ws = wb.Sheets("Sheet1")
Set co = ws.ChartObjects("Chart1")
With co.Chart
Debug.Print .Name
For c = 1 To .SeriesCollection.Count
Debug.Print .SeriesCollection(c).Name
Debug.Print "Foreground Color: " & .SeriesCollection(c).MarkerForegroundColorIndex 'Get Forground Color
Debug.Print "Background Color: " & .SeriesCollection(c).MarkerBackgroundColorIndex ' Get Background color
.SeriesCollection(c).MarkerForegroundColorIndex = 5 'Set foreground to Blue
.SeriesCollection(c).MarkerBackgroundColorIndex = 42 ' Set background to Lt Blue
Next c
End With
End Sub
After re-reading your question, I dug a little deeper to see how to check or set the color for a Point. All you should need to do is embed another FORloop inside of the above code. The important thing in this line is to use: .SeriesCollection(c).Points.Item(i).Marker... = Value
重新阅读您的问题后，我深入研究了如何检查或设置Point. 您需要做的就是FOR在上述代码中嵌入另一个循环。这一行中重要的是使用： .SeriesCollection(c).Points.Item(i).Marker... = Value
