excel - 如何根据excel中另一个工作表单元格值的特定值填充单元格背景颜色？

Question

如何根据excel中另一个工作表单元格值的特定值填充单元格背景颜色？

我想根据 Sheet1 单元格值填充 sheet2 单元格。

表 1

表 2

例如，在 sheet1 value 4 missing so 中，不需要像这样填写包含 value 04 的 sheet2 单元格。

请帮我。

标签： excelvbaexcel-formula

Answer 1

试一下：

Sub test()

    Dim rng1 As Range, rng2 As Range, cell As Range, rngFound As Range
    Dim ws1 As Worksheet, ws2 As Worksheet

    With ThisWorkbook
        Set ws1 = .Worksheets("Sheet1")
        Set ws2 = .Worksheets("Sheet2")
    End With

    With ws1
        Set rng1 = .Range("A1:R1")
    End With

    With ws2

        Set rng2 = .Range("A1:E5")

        For Each cell In rng2

            Set rngFound = rng1.Find(cell.Value, LookIn:=xlValues, Lookat:=xlWhole)

            If Not rngFound Is Nothing Then

                cell.Interior.ColorIndex = rngFound.Interior.ColorIndex

            End If

        Next cell

    End With

End Sub