VBA 代码需要很长时间才能执行

Question

以下 VBA 代码需要很长时间才能执行

Dim rngCol1 As Range
Dim rngCol2 As Range
Dim myvalue As Long
Dim c As Range

Set rngCol1 = ThisWorkbook.Sheets("Reviews").Range("D1:D30" & Range("D" & Rows.Count).End(xlUp).Row)
Set rngCol2 = ThisWorkbook.Sheets("Input").Range("M3")

For Each c In rngCol1
    On Error Resume Next
    If IsError(myvalue = WorksheetFunction.Match(c.Value, rngCol2, 0)) Then
    Else
        c.Font.Color = vbRed
    End If
Next

如果可以修改此代码以快速运行。
或者，如果在单元格编号“M3”的工作表“输入”中匹配时，可以写入任何替代 VBA 代码以突出显示列范围“D1:D30”的工作表“评论”中的值(红色)。谢谢

Answer 1

我会用条件格式规则来解决这个问题。

Dim addr As String
With ActiveWorkbook.Worksheets("Reviews")
    With .Range(.Cells(1, "D"), .Cells(.Rows.Count, "D").End(xlUp))
        addr = .Cells(1).Address(False, True)
        .FormatConditions.Delete
        With .FormatConditions.Add(Type:=xlExpression, Formula1:="=and(" & addr & "='Input'!$M$3, not(isblank(" & addr & ")))")
            .Interior.Color = 255    'red
        End With
    End With
End With