vba 自动设置图表系列颜色以按类别而非系列匹配源单元格颜色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22924456/
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
Automatically Set Chart Series Colors to Match Source Cell Colors by Category not Series
提问by user3508794
I have a VBA code to automatically change the color in a chart, which I found from this site: http://datapigtechnologies.com/blog/index.php/automatically-set-chart-series-colors-to-match-source-cell-colors/
我有一个 VBA 代码可以自动更改图表中的颜色，我是从这个网站找到的：http: //datapigtechnologies.com/blog/index.php/automatically-set-chart-series-colors-to-match-source -单元格颜色/
The code is posted below. My problem is that I need this code to apply to the chart's horizontal categories instead of the series because I am using a horizontal bar chart and the data has to be arranged in this way. How can I change the VBA to apply the automatic color change to the categories?
代码贴在下面。我的问题是我需要将此代码应用于图表的水平类别而不是系列，因为我使用的是水平条形图并且数据必须以这种方式排列。如何更改 VBA 以将自动颜色更改应用于类别？
Sub CellColorsToChart()
Dim oChart As ChartObject
Dim MySeries As Series
Dim FormulaSplit As Variant
Dim SourceRange As Range
Dim SourceRangeColor As Long
'Loop through all charts in the active sheet
For Each oChart In ActiveSheet.ChartObjects
'Loop through all series in the target chart
For Each MySeries In oChart.Chart.SeriesCollection
'Get Source Data Range for the target series
FormulaSplit = Split(MySeries.Formula, ",")
'Capture the first cell in the source range then trap the color
Set SourceRange = Range(FormulaSplit(2)).Item(1)
SourceRangeColor = SourceRange.Interior.Color
On Error Resume Next
'Coloring for Excel 2003
MySeries.Interior.Color = SourceRangeColor
MySeries.Border.Color = SourceRangeColor
MySeries.MarkerBackgroundColorIndex = SourceRangeColor
MySeries.MarkerForegroundColorIndex = SourceRangeColor
'Coloring for Excel 2007 and 2010
MySeries.MarkerBackgroundColor = SourceRangeColor
MySeries.MarkerForegroundColor = SourceRangeColor
MySeries.Format.Line.ForeColor.RGB = SourceRangeColor
MySeries.Format.Line.BackColor.RGB = SourceRangeColor
MySeries.Format.Fill.ForeColor.RGB = SourceRangeColor
Next MySeries
Next oChart
End Sub
回答by esde84
The following code does something along the lines you are asking. It iterates through the points and colours them to the background colour of the cell and iterates through multiple series if they exist.
以下代码按照您的要求执行某些操作。它遍历点并将它们着色为单元格的背景颜色，并遍历多个系列（如果存在）。
Sub CellColorsToChart()
Dim oChart As ChartObject
Dim MySeries As Series
Dim FormulaSplit As Variant
Dim SourceRangeColor As Long
Dim seriesArray() As Variant
Dim pointIterator As Integer
For Each oChart In ActiveSheet.ChartObjects
For Each MySeries In oChart.Chart.SeriesCollection
seriesArray = MySeries.Values
For pointIterator = 1 To UBound(seriesArray)
FormulaSplit = Split(MySeries.Formula, ",")
SourceRangeColor = Range(FormulaSplit(2)).Item(pointIterator).Interior.Color
MySeries.Points(pointIterator).Interior.Color = SourceRangeColor
Next pointIterator
Next MySeries
Next oChart
End Sub
