vba 如何通过VBA代码获取Excel 2012条件格式的色阶颜色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9773357/
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
How to get colors made by color scale of conditional formatting of Excel 2012 through VBA code
提问by Jure
I need to know: how to get colors made by color scale of conditional formatting of Excel 2010 throught VBA code. Those colors will be subsequently assigned by VBA as chart background according to the following image:
我需要知道：如何通过 VBA 代码获取由 Excel 2010 条件格式的色标制作的颜色。这些颜色随后将根据下图由 VBA 指定为图表背景：
www.lnkm.cz/Slozka/Example.jpg http://www.lnkm.cz/Slozka/Example.jpg
www.lnkm.cz/Slozka/Example.jpg http://www.lnkm.cz/Slozka/Example.jpg
I did a research on various web sides and:
我对各种网络方面进行了研究，并且：
- Most people advice how to read color of conditional formatting by method
<Cell>.FormatConditions(index that is active).Interior.ColorIndexbut in my case it don't work because of error “Object doesn't support this property or method” - Some people advice to write own computation of colors (based on cells value). I found various ways how to do it, but none of them can compute same colors as was computed previously by excel (same colors as are on previous picture).
- 大多数人建议如何通过方法读取条件格式的颜色，
<Cell>.FormatConditions(index that is active).Interior.ColorIndex但在我的情况下它不起作用，因为错误“对象不支持此属性或方法” - 有些人建议编写自己的颜色计算（基于单元格值）。我找到了各种方法，但没有一种方法可以计算出与 excel 之前计算的相同的颜色（与上一张图片中的颜色相同）。
So I'm asking:
所以我问：
- Is there any way to directly ready colors from cells? (or those colors are not accessible for API)
- Do you know how to compute same colors as excel compute?
- Do you know any other way how to solve my problem?
- 有没有办法直接从细胞中准备颜色？（或者 API 无法访问这些颜色）
- 你知道如何计算与excel计算相同的颜色吗？
- 你知道如何解决我的问题的其他方法吗？
I believe that it has to work somehow.
我相信它必须以某种方式起作用。
采纳答案by Aprillion
if no better answer is provided, you can try this workaround:
如果没有提供更好的答案，您可以尝试以下解决方法：
- link / copy your data to cells under the chart (with formulas like
=Sheet1!A1) - apply the same conditional formatting
- hide the values (with custom number format like
"", i.e. empty string literal (2 double quotes)) - make the chart transparent
- align the cells with the chart
- 将您的数据链接/复制到图表下的单元格（使用公式如
=Sheet1!A1） - 应用相同的条件格式
- 隐藏值（使用自定义数字格式，如
""，即空字符串文字（2 个双引号）） - 使图表透明
- 将单元格与图表对齐
UPDATE:
更新：
or you can try to compute the color by linear approximation for each R, G, B channel if the conditional format uses only 2 base colors (r1, g1, b1) and (r2, g2, b2) for 2 corner cases which can be
或者，如果条件格式仅使用 2 个基色 (r1, g1, b1) 和 (r2, g2, b2) 用于 2 个极端情况，则您可以尝试通过线性近似为每个 R、G、B 通道计算颜色
minandmaxvalue, e.g.: 0 - 4 000minandmaxpercent, e.g.: 10% - 90%
(i believe you can use % * [max_value - min_value] to get the actual value)minandmaxpercentile, e.g.: 0th percentile - 100th percentile
min和maxvalue，例如：0 - 4 000min和max百分比，例如：10% - 90%
（我相信你可以使用 % * [max_value - min_value] 来获得实际值）min和max百分位数，例如：第 0 个百分位数 - 第 100 个百分位数
for percent / percentile options you first need to convert an actual value to the percent / percentile value, then if value < minor value > maxuse the corner colors, otherwise:
对于百分比/百分位选项，您首先需要将实际值转换为百分比/百分位值，然后如果value < min或value > max使用角颜色，否则：
r = r1 + (r2 - r1) * (value - min_value) / (max_value - min_value)
g = ...
b = ...
回答by Tim Williams
This will copy a picture of a cell to the top-left corner of a chartobject on the same worksheet. Note the picture is linked to the copied cell - if the value or formatting color changes it will change to match.
这会将单元格的图片复制到同一工作表上图表对象的左上角。请注意，图片链接到复制的单元格 - 如果值或格式颜色更改，它将更改以匹配。
Sub Tester()
CopyLinkedPicToPlot ActiveSheet.Range("E4"), "Chart 2"
End Sub
Sub CopyLinkedPicToPlot(rngCopy As Range, chtName As String)
Dim cht As ChartObject
Set cht = ActiveSheet.ChartObjects(chtName)
rngCopy.Copy
With rngCopy.Parent.Pictures.Paste(Link:=True)
.Top = cht.Top
.Left = cht.Left
End With
End Sub
EDIT: I just tested this with a fairly small 4x8 matrix of cells/charts and the performance is pretty bad! Might be better just pasting without Link:=True ...
编辑：我刚刚用一个相当小的 4x8 单元格/图表矩阵测试了这个，性能非常糟糕！不使用 Link:=True 粘贴可能会更好...
回答by RP2
This is not specific to your problem but is easily modified to solve your problem...
这不是特定于您的问题，而是很容易修改以解决您的问题......
Sub CopyCondFill()
Dim FromSheet As Object
Dim ToSheet As Object
Dim FromSheetName as String
Dim ToSheetName as String
Dim ToRange As Range
Dim StrRange As String
'''Sheet with formatting you want to copy
FromSheetName = "YourSheetsName"
Set FromSheet = Application.ThisWorkbook.Sheets(FromSheetName )
'''Start of range within sheet you want to copy
FromFirstRow = 3
FromFirstCol = 2
'''Sheet you want to copy formatting to
ToSheetName = "YourSheetsName"
Set ToSheet = Application.ThisWorkbook.Sheets(ToSheetName)
'''range to copy formatting to
ToFirstRow = 3
ToFirstCol = 2
'''NOTE: Adjust row/column to take lastrow/lastcol from or enter value manually
ToLastRow = FromSheet.Cells(Rows.Count, 1).End(xlUp).Row
ToLastCol = FromSheet.Cells(2, Columns.Count).End(xlToLeft).Column
Set ToRange = ToSheet.Range(Cells(ToFirstRow, ToFirstCol), Cells(ToLastRow, ToLastCol))
'''Apply formatting to range
For Each cell In ToRange
StrRange = cell.Address(0, 0)
ToSheet.Range(StrRange).Offset(ToFirstRow - FromFirstRow, ToFirstCol - FromFirstCol).Interior.Color = _
FromSheet.Range(StrRange).DisplayFormat.Interior.Color
Next cell
End Sub
回答by Hamed
Try this:
尝试这个：
<Cell>.DisplayFormat.Interior.Color
This should word with Excel later than 2010.
这应该在 2010 年以后使用 Excel。
回答by CodeKid
This worked for me, based on the answer of JKirchartz
根据 JKirchartz 的回答，这对我有用
Sub copyBackgroundColors(source As Range, target As Range)
target.Interior.color = source.DisplayFormat.Interior.color
End Sub
回答by Tony Dallimore
This is a partial answer to your question. Column 1 of the table below lists Excel's standard 40 colours. Columns 2, 3 and 4 list the red, green and blue components of each colour. So if you want a cell's font to be light orange:
这是对您问题的部分回答。下表的第 1 列列出了 Excel 的标准 40 种颜色。第 2、3 和 4 列列出了每种颜色的红色、绿色和蓝色成分。因此，如果您希望单元格的字体为浅橙色：
Cell(Row, Column).Font.Color = RGB(255, 153, 0)
If you try any other red-green-combination, Excel will match it to the nearest one of these standard colours although Excel's idea of "nearest" does not match mine.
如果您尝试任何其他红绿组合，Excel 会将其与这些标准颜色中最接近的一种进行匹配，尽管 Excel 的“最近”的想法与我的不符。
Hope this helps if you get the other part of your question answered.
如果您回答了问题的其他部分，希望这会有所帮助。
Colour Red Green Blue
Black 0 0 0
Light orange 255 153 0
Lime 153 204 0
Gold 255 204 0
Bright green 0 255 0
Yellow 255 255 0
Grey 80% 51 51 51
Dark teal 0 51 102
Plum 153 51 102
Sea green 51 153 102
Dark blue 0 0 128
Dark red 128 0 0
Violet 128 0 128
Teal 0 128 128
Grey 50% 128 128 128
Grey 40% 150 150 150
Indigo 51 51 153
Blue-grey 102 102 153
Tan 255 204 153
Light yellow 255 255 153
Grey 25% 192 192 192
Aqua 51 204 204
Red 255 0 0
Rose 255 153 204
Light green 204 255 204
Blue 0 0 255
Pink 255 0 255
Light blue 51 102 255
Lavender 204 153 255
Sky blue 0 204 255
Pale blue 153 204 255
Turquoise 0 255 255
Light turquoise 204 255 255
Dark green 0 51 0
White 255 255 255
Olive green 51 51 0
Brown 153 51 0
Orange 255 102 0
Green 0 128 0
Dark yellow 128 128 0
