使用 VBA 在 Excel 电子表格中将单元格颜色更改为 RGB 值

Changing cell color to RGB values within Excel spreadsheet using VBA

提问by wilbev

I've essentially got 10 sets of RGB values in 30 columns (each value in their own cell) with repeated different RGB values in 8 rows below these. What I would like to do is color the cell of 10 cells to the right of these 30 to show a visual representation of the 10 colors (based on their set of RGB values) in all 8 of these rows. I've seen a few VBA examples out in the wild that sort of do something similar but more just on a individual set of RGB values. I was thinking I could create function like the following but that doesn't seem to work. Maybe a Sub routine would be required to do this but not sure where to start. Any help or direction would be appreciated.

我基本上在 30 列中有 10 组 RGB 值（每个值在它们自己的单元格中），在这些值下方的 8 行中重复不同的 RGB 值。我想要做的是为这 30 个右侧的 10 个单元格着色，以显示所有 8 行中 10 种颜色（基于它们的 RGB 值集）的视觉表示。我在野外看到了一些 VBA 示例，它们做了类似的事情，但更多的是针对一组单独的 RGB 值。我想我可以创建如下函数，但这似乎不起作用。也许需要一个子例程来做到这一点，但不确定从哪里开始。任何帮助或指导将不胜感激。

Function RGB_Color(R As Integer, B As Integer, G As Integer)

    RGB_Color = Application.ThisCell.Interior.Color = RGB(R, G, B)

End Function

回答by Amen Jlili

You can start with this sub. It will color a range of your desire (Rc) with data from (Rs).

你可以从这个子开始。它将使用来自 (Rs) 的数据为您想要的范围 (Rc) 着色。

Sub ColorRGB(Rs As Range, Rc As Range)
Dim R As Long
Dim G As Long
Dim B As Long
Dim Address(1 To 3) As Long
Dim I As Integer: I = 1
For Each cell In Rs.Cells
Address(I) = cell.Value
I = I + 1
Next
R = Address(1)
G = Address(2)
B = Address(3)
Rc.Interior.Color = RGB(R, G, B)
End Sub

Test:

测试：

In this test, we're coloring the cell D1 with the contents from the range A1:C1.

在此测试中，我们使用区域 A1:C1 中的内容为单元格 D1 着色。

Sub Test()
ColorRGB Sheet1.Range("A1:C1"), Sheet1.Range("D1")
End Sub

Result:

结果：

The code below uses the RGBColor Sub. It will define a lastrowlong (8 in our case) so this can basically handle more rows. Then, it will take the first vertical range of 8 cells and set it in a Range objectcalled ronly to do a For Eachloop on it later.

下面的代码使用 RGBColor Sub。它将定义一个lastrowlong（在我们的例子中是 8 个），所以这基本上可以处理更多的行。然后，它将采用 8 个单元格的第一个垂直范围并将其设置在一个Range object调用中，以便稍后r对其进行For Each循环。

With every cell in the For Eachloop, we're going to run with another Forloop transversely (in direction of columns). The new Forloop will run with step of 3 calling our ColorRGB function and telling it to take in the arguments supplied below.

对于For Each循环中的每个单元格，我们将For横向运行另一个循环（在列的方向上）。新For循环将以第 3 步运行，调用我们的 ColorRGB 函数并告诉它接受下面提供的参数。

Given the fact that the Forloop runs with a step of 3, we've defined a counter for the colored cells called cthat increments itself by 1 with each step of the Forloop (its step is egal to 3).

鉴于For循环以 3 的步长运行，我们为彩色单元格定义了一个计数器，称为c该计数器随着For循环的每一步（其步长等于 3）自增 1。

I hope this logic is clear. There are obviously better methods to do this.

我希望这个逻辑是清楚的。显然有更好的方法可以做到这一点。

Sub ColorSheet(sheetname As string)
Dim r As Range
'defining lastrow which is 8
Dim lastrow As Long
With ThisWorkbook.Worksheets(sheetname)
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set r = .Range("A1", "A" & lastrow)
End With
Dim C As Integer: C = 30
For Each cell In r.Cells
For I = 1 To 30 Step 3
ColorRGB ThisWorkbook.Worksheets(sheetname).Range(cell.Offset(0, I - 1).Address(0, 0), cell.Offset(0, I + 1).Address(0, 0)), ThisWorkbook.Worksheets(sheetname).Range(cell.Offset(0, C).Address(0, 0))
C = C + 1
Next I
C = 30
Next
End Sub

This final sub will color the sheet name you provide it with.

最后一个子项将为您提供的工作表名称着色。

Sub Test
ColorSheet("Sheet3")
'And so on...
End Sub

If your sheets are named like SheetX

如果您的工作表命名为 SheetX

    Sub Test
    Dim I as Integer
    For I = 1 to 20
    ColorSheet("Sheet"&I)
    Next I
    End Sub

Example:

例子：

回答by Walton C

Yours doesn't work because you're passing RGB_Color(R,B,G) to RGB(R,G,B)

你的不起作用，因为你将 RGB_Color(R,B,G) 传递给 RGB(R,G,B)