根据另一个单元格值更改单元格颜色

In a workbook I have, the D column has a formula in it to derive the last six digits of a value in column C. These columns are located in a sheet titled "JE". I have a dynamic SQL connected query that has values in the A column. That query is located in a sheet titled "required_refs". I essentially, want to write: If the value in the D column cell matches/equals any of the values in that query in sheet "required_refs", turn the F column cell red in sheet JE.

在我的工作簿中，D列中有一个公式，用于导出C列中值的后六位数。这些列位于标题为“JE”的工作表中。我有一个动态SQL连接查询，其中包含A列中的值。该查询位于标题为“required_refs”的工作表中。本质上，我想写：如果D列单元格中的值与表格“required_refs”中该查询中的任何值匹配/等于，则在表格JE中将F列单元格变为红色。

Example: If cell D10 has a value that equals any of the values in column A in "required_refs", turn cell F10 red. In addition, if cell D13 has a value that matches/equals a value in column A in sheet "required_refs", turn F13 red. And so on.

示例：如果单元格D10的值等于“required_refs”中A列中的任何值，请将单元格F10变为红色。此外，如果单元格D13的值与表格“required_refs”中列A中的值匹配/等于，则将F13变为红色。等等。

Here is the code I tried. I added it in Sheet "JE": Code:

这是我试过的代码。我在Sheet“JE”中添加了它：代码：

  Sub ChangeCellColor()

    Dim ref_code As Range: Set ref_code = Range("D7:D446").Value
    Dim refCode_Confirm As Range: Set refCode_Confirm = Worksheets("required_refs").Range("A:A").Value
    Dim colorChange As Range: Set colorChange = Worksheets("required_refs").Range("A:A")

    For Each cell In ref_code
        If cell.Value = refCode_Confirm.Value Then
            Range("F7:F446").ActiveCell.Interior.ColorIndex = 3
            Next cell
        End If
    End Sub

Currently, this code just doesn't do anything. It doesn't turn the F column cell red. I've asked a question similar to this but, the workbook I'm using has changed a bunch since then, and this question is a bit more simple than the previous one. If anyone could help, I'd really appreciate it. Thanks!

目前，此代码不做任何事情。它不会将F列单元格变为红色。我问了一个与此类似的问题但是，我使用的工作簿从那以后改变了一堆，这个问题比前一个问题简单得多。如果有人可以提供帮助，我真的很感激。谢谢！

1 个解决方案

#1

Your code has a number of issues.

您的代码有很多问题。

.Value returns a basic type, like a string or long. You can't assign this to a range variable.
.Value返回一个基本类型，如字符串或long。您无法将其分配给范围变量。
Your End If and Next cell statements are swapped around. Always use correct indentation so these errors become more obvious.
您的End If和Next单元格语句被交换。始终使用正确的缩进，以便这些错误变得更加明显。
You have an undeclared variable cell. This can potentially cause bugs. In the VBE, turn on the Tools > Options > Editor > Required Variable Declaration option to force the use of Option Explicit in new modules.
你有一个未声明的变量单元格。这可能会导致错误。在VBE中，打开工具>选项>编辑器>必需变量声明选项以强制在新模块中使用Option Explicit。

Fixing these issues leads us to this:

解决这些问题导致我们：

Sub ChangeCellColor()

  Dim cell As Range
  Dim ref_code As Range: Set ref_code = Range("D7:D446")
  Dim refCode_Confirm As Range: Set refCode_Confirm = Worksheets("required_refs").Range("A:A")
  Dim colorChange As Range: Set colorChange = Worksheets("required_refs").Range("A:A")

  For Each cell In ref_code
    If cell.Value = refCode_Confirm.Value Then
      Range("F7:F446").ActiveCell.Interior.ColorIndex = 3
    End If
  Next cell

End Sub

Unfortunately, it still doesn't work as you can't compare a single value directly against a column of values in VBA.

不幸的是，它仍然无法正常工作，因为您无法直接将单个值与VBA中的一列值进行比较。

This following code corrects this remaining issue. Note the choosing of good meaningful names as well as the use of RVBA for the variables. This is a good tip for how to avoid making similar errors. Also note the use of .Value2 instead of .Value. This is highly recommended.

以下代码更正了此剩余问题。请注意选择好的有意义的名称以及RVBA用于变量。这是一个很好的提示，如何避免类似的错误。另请注意使用.Value2而不是.Value。强烈建议这样做。

Sub ChangeCellColor()

  Dim rngRef As Range
  Dim rngRefsToCheck As Range: Set rngRefsToCheck = Range("D7:D446")
  Dim rngRequiredRefs As Range: Set rngRequiredRefs = Worksheets("required_refs").Columns("A")
  Dim rngColorChangeRequired As Range: Set rngColorChangeRequired = Columns("F")

  For Each rngRef In rngRefsToCheck
    If Not IsError(Application.Match(rngRef.Value2, rngRequiredRefs, 0)) Then
      rngColorChangeRequired.Cells(rngRef.Row).Interior.ColorIndex = 3
    End If
  Next rngRef

End Sub

The best and fastest way to achieve the color change would be to use Advanced Filters, thus avoiding the need to loop. However, since you're still learning the basics, I've shown the looping version.

实现颜色变化的最佳和最快方法是使用高级过滤器，从而避免循环。但是，由于你还在学习基础知识，我已经展示了循环版本。

#1