Excel:将单元格背景色设置为单元格中数据的rgb值

I have a column containing rgb values, eg

我有一个包含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中，有什么方法可以用这个来设置单元格的背景颜色?

4 个解决方案

#1

You can use VBA - something like

你可以使用VBA -类似的东西

Range("A1:A6").Interior.Color = RGB(127,187,199)

Just pass in the cell value.

只需传入单元格值。

#2

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).

单独设置颜色属性将保证精确匹配。Excel 2003只能同时处理56种颜色。好消息是，您可以为这56个槽(称为ColorIndexs)分配任何rgb值。当您使用颜色属性设置一个单元格的颜色时，这将使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 all cells 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更改为上述值。但是，您应该意识到，通过更改颜色索引的值，您可以更改在工作簿中使用该颜色的所有单元格的颜色。举个例子，红色是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 ColorIndex so 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

#3

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 A2 to C2. The font color is a contrasting white or black.

一旦将鼠标悬停在单元格上(试试它!)，背景色将更新从单元格A2到C2的RGB。字体颜色是对比色的白色或黑色。

#4

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()组合它们。

#1