vba 根据范围有条件地更改Excel VB中条形图中单个条形的颜色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9703556/
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
Conditionally change colors of individual bars in bar chart in Excel VB based on range
提问by cupcakes
I am working on an Excel project and am trying to format the colors of a bar chart (and later a pie chart by the same reasoning) in order to display RED, GREEN, or YELLOWbased on another range of data. The data range is...
我在一个Excel项目的工作，我试图以格式化条形图（后来被同样的道理饼图）的颜色来显示RED，GREEN或YELLOW基于数据的另一个范围。数据范围是...
Sheet: Overview
表：Overview
Range: E15:E36
范围：E15:E36
These values are percentages. Based on what percentage it falls between, I'd like the bars to be formatted green, red or yellow.
这些值是百分比。根据介于两者之间的百分比，我希望将条形设置为绿色、红色或黄色。
If between 100 - 90, Green If between 89 - 70, Yellow If between 69 - 1, Red
如果在 100 - 90 之间，绿色 如果在 89 - 70 之间，黄色 如果在 69 - 1 之间，红色
Below is my code to this point (for the bar chart):
下面是我的代码（对于条形图）：
Sub Macro2()
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Overview").Range("A15:A36")
ActiveChart.SetSourceData Source:=Sheets("Overview").Range("A15:A36,B15:B36")
ActiveChart.ApplyLayout (2)
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.Legend.Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.ChartTitle.Select
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.ChartTitle.Text = "Rating Site Distribution"
End Sub
Any help would be greatly appreciated! I'm not at all familiar with VBA and feel entirely out of my element on this one...
任何帮助将不胜感激！我对 VBA 一点都不熟悉，并且对这个完全不了解...
Also, would the same function work for a pie chart to define the color by the same parameters?
此外，相同的功能是否适用于饼图以通过相同的参数定义颜色？
Thank in advance!!
预先感谢！！
回答by whytheq
here a vba function I use to invert negative bars so they are red. Maybe this can be adapted:
这是我用来反转负条的 vba 函数，因此它们是红色的。也许这可以适应：
The function is called from a sub routine in the a module in the workbook like this:
该函数是从工作簿中 a 模块中的子例程调用的，如下所示：
Sub FixTheGraph()
FormatGraph("Overview")
End Sub
Here's the Function. You can just paste it below the sub-routine:
这是函数。您可以将其粘贴在子程序下方：
Function FormatGraph(myWorksheet As String)
Excel.Sheets(myWorksheet).Select
Dim myChartObject As ChartObject
For Each myChartObject In Excel.ActiveSheet.ChartObjects
myChartObject.Select
Dim myPoint As Integer, valArray
With Excel.ActiveChart.SeriesCollection(1)
valArray = .Values
For myPoint = 1 To .Points.Count
'myVal = valArray(myPoint)
Select Case valArray(myPoint)
Case 0.9 To 1
With .Points(myPoint)
.Interior.ColorIndex = 1 '<change colour to suit
End With
Case 0.7 To 0.9
With .Points(myPoint)
.Interior.ColorIndex = 5 '<change colour to suit
End With
Case Is < 0.7
With .Points(myPoint)
.Interior.ColorIndex = 3
End With
End Select
Next
End With
Next myChartObject
Excel.Sheets(myWorksheet).Cells(1, 1).Select
End Function
回答by Marc Thibault
What you want to do is a chart type xlBarStacked. Then give it three series - one for each color.
您想要做的是图表类型 xlBarStacked。然后给它三个系列 - 每个颜色一个。
In each series, if the element value is in range, use the value, otherwise set the series.Value element to 0.
在每个系列中，如果元素值在范围内，则使用该值，否则将 series.Value 元素设置为 0。
Since your ranges are mutually exclusive, each bar will have only one color.
由于您的范围是互斥的，因此每个条形将只有一种颜色。
