vba 将单元格的背景颜色设置为单元格中数据的RGB值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1426369/
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
Set background colour of cell to RGB value of data in cell
提问by Internet man
I have a column containing RGB values, e.g.:
我有一列包含 RGB 值，例如：
127,187,199
67,22,94
In Excel, is there any way I can use this to set the background colour of the cell?
在 Excel 中，有什么方法可以使用它来设置单元格的背景颜色？
回答by Galwegian
You can use VBA - something like
您可以使用 VBA - 类似
Range("A1:A6").Interior.Color = RGB(127,187,199)
Just pass in the cell value.
只需传入单元格值。
回答by Oorang
Setting the Color property alone will guarantee an exact match. Excel 2003 can only handle 56 colors at once. The good news is that you can assign any rgb value at all to those 56 slots (which are called ColorIndexs). When you set a cell's color using the Color property this causes Excel to use the nearest "ColorIndex". Example: Setting a cell to RGB 10,20,50 (or 3281930) will actually cause it to be set to color index 56 which is 51,51,51 (or 3355443).
单独设置 Color 属性将保证完全匹配。Excel 2003 一次只能处理 56 种颜色。好消息是您可以为这 56 个插槽（称为 ColorIndex）分配任何 rgb 值。当您使用 Color 属性设置单元格的颜色时，这会导致 Excel 使用最接近的“ColorIndex”。示例：将单元格设置为 RGB 10,20,50（或 3281930）实际上会使其设置为颜色索引 56，即 51,51,51（或 3355443）。
If you want to be assured you got an exact match, you need to change a ColorIndex to the RGB value you want and then change the Cell's ColorIndex to said value. However you should be aware that by changing the value of a color index you change the color of allcells already using that color within the workbook. To give an example, Red is ColorIndex 3. So any cell you made Red you actually made ColorIndex 3. And if you redefine ColorIndex 3 to be say, purple, then your cell will indeed be made purple, but all other red cells in the workbook will also be changed to purple.
如果您想确保获得完全匹配，则需要将 ColorIndex 更改为所需的 RGB 值，然后将单元格的 ColorIndex 更改为所述值。但是，您应该知道，通过更改颜色索引的值，您会更改工作簿中已使用该颜色的所有单元格的颜色。举个例子，Red 是 ColorIndex 3。所以你把 ColorIndex 设置为 3 的任何单元格实际上都是 ColorIndex 3。如果你重新定义 ColorIndex 3 为紫色，那么你的单元格确实会变成紫色，但所有其他红色单元格在工作簿也将更改为紫色。
There are several strategies to deal with this. One way is to choose an index not yet in use, or just one that you think will not be likely to be used. Another way is to change the RGB value of the nearest ColorIndexso your change will be subtle. The code I have posted below takes this approach. Taking advantage of the knowledge that the nearest ColorIndex is assigned, it assigns the RGB value directly to the cell (thereby yielding the nearest color) and then assigns the RGB value to that index.
有几种策略可以解决这个问题。一种方法是选择尚未使用的索引，或者仅选择您认为可能不会使用的索引。另一种方法是更改最近的 ColorIndex的 RGB 值，这样您的更改就会很微妙。我在下面发布的代码采用了这种方法。利用最近的 ColorIndex 已分配的知识，它将 RGB 值直接分配给单元格（从而产生最接近的颜色），然后将 RGB 值分配给该索引。
Sub Example()
Dim lngColor As Long
lngColor = RGB(10, 20, 50)
With Range("A1").Interior
.Color = lngColor
ActiveWorkbook.Colors(.ColorIndex) = lngColor
End With
End Sub
回答by Michel de Ruiter
Cells cannot be changed from within a VBA function used as a worksheet formula. Except via this workaround...
不能从用作工作表公式的 VBA 函数中更改单元格。除了通过这个解决方法...
Put this function into a new module:
将此函数放入一个新模块中：
Function SetRGB(x As Range, R As Byte, G As Byte, B As Byte)
On Error Resume Next
x.Interior.Color = RGB(R, G, B)
x.Font.Color = IIf(0.299 * R + 0.587 * G + 0.114 * B < 128, vbWhite, vbBlack)
End Function
Then use this formula in your sheet, for example in cell D2:
然后在您的工作表中使用此公式，例如在单元格中D2：
=HYPERLINK(SetRGB(D2;A2;B2;C2);"HOVER!")
Once you hover the mouse over the cell (try it!), the background color updates to the RGB taken from cells A2to C2. The font color is a contrasting white or black.
一旦您将鼠标悬停在单元格上（试试看！），背景颜色将更新为从单元格获取的 RGBA2到C2. 字体颜色是对比鲜明的白色或黑色。
回答by Ben Aveling
To color each cell based on its current integer value, the following should work, if you have a recent version of Excel. (Older versions don't handle rgb as well)
要根据每个单元格的当前整数值为其着色，如果您有最新版本的 Excel，以下应该可以工作。（旧版本也不能处理 rgb）
Sub Colourise()
'
' Colourise Macro
'
' Colours all selected cells, based on their current integer rgb value
' For e.g. (it's a bit backward from what you might expect)
' 255 = #ff0000 = red
' 256*255 = #00ff00 = green
' 256*256*255 #0000ff = blue
' 255 + 256*256*255 #ff00ff = magenta
' and so on...
'
' Keyboard Shortcut: Ctrl+Shift+C (or whatever you want to set it to)
'
For Each cell In Selection
If WorksheetFunction.IsNumber(cell) Then
cell.Interior.Color = cell.Value
End If
Next cell
End Sub
If instead of a number you have a string then you can split the string into three numbers and combine them using rgb().
如果您有一个字符串而不是数字，那么您可以将字符串拆分为三个数字并使用 rgb() 将它们组合起来。
回答by Amrit Pal Singh
Sub AddColor()
For Each cell In Selection
R = Round(cell.Value)
G = Round(cell.Offset(0, 1).Value)
B = Round(cell.Offset(0, 2).Value)
Cells(cell.Row, 1).Resize(1, 4).Interior.Color = RGB(R, G, B)
Next cell
End Sub
Assuming that there are 3 columns R, G and B (in this order). Select first column ie R. press alt+F11 and run the above code. We have to select the first column (containing R or red values) and run the code every time we change the values to reflect the changes.
假设有 3 列 R、G 和 B（按此顺序）。选择第一列，即 R。按 alt+F11 并运行上面的代码。我们必须选择第一列（包含 R 或红色值）并在每次更改值以反映更改时运行代码。
I hope this simpler code helps !
我希望这个更简单的代码有帮助！
