Excel VBA:匹配单元格颜色 [英] Excel VBA: Match Cell Color

问题描述

我有一本有两张纸的工作簿.在工作表A上，我更改了某些单元格的内部颜色.我想在工作表B中找到具有匹配文本的单元格，并将它们设置为具有相同的内部颜色.但是，当我进入hRow = Application...时，会收到一个错误，我一直在寻找The application does not support this object or property.相似的函数，但是我没有成功找到匹配文本而不循环遍历范围中每个单元格的好方法.

I have a workbook with two sheets. On Sheet A, I have changed the interior color of some cells. I would like to find cells in Sheet B with matching text and set them to have the same interior color. However, when I get to hRow = Application..., I receive an error that The application does not support this object or property. I've been searching for similar functions, but I am not having any success finding a good way to match text without looping through each cell in a range.

Public Sub MatchHighlight()

Dim lRow As Integer
Dim i As Integer
Dim hRow As Integer

Dim LookUpRange As Range
Set LookUpRange = Worksheets("HR - Highlight").Range("C2:C104")

Dim compare As Range
Set compare = Worksheets("Full List").Range("C2:C277")

lRow = Worksheets("Full List").UsedRange.Rows.Count

For i = 2 To lRow

    hRow = Application.Worksheets("Full List").WorksheetFunction.Match(compare.Range("C" & i).Text, LookUpRange, 0)

    If Not IsNull(hRow) Then

        compare.Range("C" & i).Interior.Color = LookUpRange.Range("C" & hRow).Interior.Color

    End If

Next i

结束子

推荐答案

Sub MatchHighlight()

    Dim wsHighlight As Worksheet
    Dim wsData As Worksheet
    Dim rngColor As Range
    Dim rngFound As Range
    Dim KeywordCell As Range
    Dim strFirst As String

    Set wsHighlight = Sheets("HR - Highlight")
    Set wsData = Sheets("Full List")

    With wsData.Columns("C")
        For Each KeywordCell In wsHighlight.Range("C2", wsHighlight.Cells(Rows.Count, "C").End(xlUp)).Cells
            Set rngFound = .Find(KeywordCell.Text, .Cells(.Cells.Count), xlValues, xlWhole)
            If Not rngFound Is Nothing Then
                strFirst = rngFound.Address
                Set rngColor = rngFound
                Do
                    Set rngColor = Union(rngColor, rngFound)
                    Set rngFound = .Find(KeywordCell.Text, rngFound, xlValues, xlWhole)
                Loop While rngFound.Address <> strFirst
                rngColor.Interior.Color = KeywordCell.Interior.Color
            End If
        Next KeywordCell
    End With

End Sub

这篇关于Excel VBA:匹配单元格颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！