VBA-根据特定单元格中的值更改单元格的颜色

I want to change the background colors of cells A2:C2 based on the value of cell D2.

我想根据单元格D2的值更改单元格A2:C2的背景颜色。

This also applies to the relative cells in rows 3,4, and 5.

这也适用于行3,4和5中的相对单元格。

If the value in cell D# is 1, I'd like color x. If the value is 2, I'd like color y, if the value is 3, I'd like the color z.

如果单元格D#中的值是1,我想要颜色x。如果值为2,我想要颜色y,如果值为3,我想要颜色z。

If it makes a difference, the target range (A2:D6) will be in a table format.

如果它有所不同,目标范围(A2:D6)将采用表格格式。

I'd like this subroutine to execute upon opening the workbook. I know where to put that subroutine so don't sweat instructing me how.

我想在打开工作簿时执行此子例程。我知道在哪里放这个子程序所以不要冒汗指示我如何。

I've done this with conditional formatting, but it'd be nice to have some VBA I can copy-pasta into future reports.

我已经使用条件格式化完成了这项工作,但是有一些VBA我可以将面板复制到未来的报告中。

1 个解决方案

#1

You should use Conditional formatting, but this works:

您应该使用条件格式,但这适用:

Sub ColorMeElmo()
   Dim i As Long, r1 As Range, r2 As Range

   For i = 2 To 5
      Set r1 = Range("D" & i)
      Set r2 = Range("A" & i & ":C" & i)
      If r1.Value = 1 Then r2.Interior.Color = vbRed
      If r1.Value = 2 Then r2.Interior.Color = vbBlue
      If r1.Value = 3 Then r2.Interior.Color = vbYellow
   Next i
End Sub