excel - 根据另一个单元格中的引用将字符串变为红色

Question

我在过去的某个时候从 Stack Overflow 中提取了以下代码，但似乎无法再次找到它。我对另一个项目有类似的需求，所以我将代码复制/粘贴到一个新模块中，现在它不起作用（相同的参考列表）。导致错误的代码部分以粗体显示。如果你删除那部分代码并继续运行，它就完成了工作并且是准确的。这几乎就像“如果语句”没有关闭。项目之间的唯一变化是注释出现的列从 Col G 变为 Col I。参考字符串列表为 100+，每日笔记列表约为 1000。参考字符串和注释的所有格式均为“常规”第一个和第二个应用程序。任何关于为什么会失败的指导将不胜感激。

'Pulled from Stack Overflow
'Searches all text in Column 2 on a Sheet for the string located in Column 1
'If found it highlights that text

Dim ThisWB As Workbook
 Dim ThisWS As Worksheet
Dim i As Integer
Dim y As Integer

Dim Col1 As Double
Dim Col2 As Double

Dim Col1_rowSTART As Double
Dim Col1_rowEND As Double

Dim Col2_rowSTART As Double
Dim Col2_rowEND As Double

Dim strTest As String
Dim strLen As Integer

'Set up parameter that we know
Set ThisWB = ActiveWorkbook
Set ThisWS = ActiveSheet
Col1 = 14 'Words Col N
Col2 = 9 'Comments Col I
'Define Starting Row for each column
Col1_rowSTART = 1
Col2_rowSTART = 1
'Define ending row for each column
Col1_rowEND = ThisWS.Cells(ThisWS.Rows.count, Col1).End(xlUp).Row
Col2_rowEND = ThisWS.Cells(ThisWS.Rows.count, Col2).End(xlUp).Row

For i = Col1_rowSTART To Col1_rowEND
    'make a string out of each cell value in Col1
    strTest = CStr(ThisWS.Cells(i, Col1))
    strLen = Len(strTest)
    'Roll thorugh all of Column 2 in search of the target string
    For y = Col2_rowSTART To Col2_rowEND
        'Check if Col1 string is in Col2 String
        If InStr(CStr(ThisWS.Cells(y, Col2)), strTest) > 0 Then
        **ThisWS.Cells(y, Col2).Characters(InStr(ThisWS.Cells(y, Col2), strTest), strLen).Font.Color 
= vbRed**
        End If
    Next y
Next i

标签： excelvba