更改图表 Excel VBA 中的点颜色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11136194/
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
Change the Point Color in chart excel VBA
提问by james
I have this chart in which if any point in graphs exceeds specific limit then its color should change.
我有这张图表，如果图表中的任何一点超过特定限制，那么它的颜色应该改变。
can anyone suggest me how to get the chart in VBA and then apply this kind of condition e.g i want to change the color of highest point in the above graph . Any help would be highly appreciated.
谁能建议我如何在 VBA 中获取图表，然后应用这种条件，例如我想更改上图中最高点的颜色。任何帮助将不胜感激。
回答by SeanC
Using: ActiveWorkbook.Sheets("Sheet1").ChartObjects("Chart1").Chart.SeriesCollection(1)
使用： ActiveWorkbook.Sheets("Sheet1").ChartObjects("Chart1").Chart.SeriesCollection(1)
Color of each point is .Points(PointNumber).Interior.Color
每个点的颜色是 .Points(PointNumber).Interior.Color
The number of points you have to cycle though is .Points.Count
你必须循环的点数是 .Points.Count
The value of each point is .Points(PointNumber).Value
每个点的值是 .Points(PointNumber).Value
colors of the markers themselves (Applies only to line, scatter, and radar charts):
标记本身的颜色（仅适用于折线图、散点图和雷达图）：
.Points(PointNumber).MarkerBackgroundColor = RGB(0,255,0) ' green
.Points(PointNumber).MarkerForegroundColor = RGB(255,0,0) ' red
.Points(PointNumber).MarkerStyle = xlMarkerStyleCircle ' change the shape
回答by Jon Peltier
Let's take another approach, which does not require any code.
让我们采用另一种方法，它不需要任何代码。
Assume your data is in columns A (sequence number or time) and B value, starting in A2 and B2, since your labels are in A1 and B1. We'll add a series to the chart that includes any deviant values from column B. This series will draw a marker in front of any deviant points so the original point will still be present, and instead of reformatting this point the new series displays a point.
假设您的数据在 A 列（序列号或时间）和 B 列中，从 A2 和 B2 开始，因为您的标签在 A1 和 B1 中。我们将向图表添加一个包含来自 B 列的任何异常值的系列。该系列将在任何异常点前绘制一个标记，因此原始点仍将存在，并且新系列不会重新格式化该点，而是显示一个观点。
In cell C1, enter "Deviant".
在单元格 C1 中，输入“Deviant”。
In Cell C2, enter a formula that detects a deviant point, something like:
在单元格 C2 中，输入检测异常点的公式，例如：
=IF(AND(B2>upperlimit,B2
=IF(AND(B2>上限，B2
This puts the value into column C if column B exceeds upper and lower limits, otherwise it puts #N/A into column C, #N/A will not result in a plotted point.
如果 B 列超过上限和下限，则将值放入 C 列，否则将 #N/A 放入 C 列，#N/A 不会导致绘制点。
Copy the data in column C, select the chart, and Paste Special as a new series. Format this series to have no line and whatever glaring marker you want to use to indicate an out of control point.
复制 C 列中的数据，选择图表，然后选择性粘贴为新系列。将此系列格式化为没有线条和任何您想要用来指示失控点的明显标记。
