我想以红色和粗体突出显示 Excel 工作表(使用 Excel 2010)的选定列中单词/短语的每个实例。例如,如果 A1:A10 列包含句子“The Brown Fox Likes the Other Brown Fox”,我想突出显示此范围内“Brown Fox”的每个实例。
我找到了一个宏here http://www.mrexcel.com/forum/excel-questions/26200-cells-there-code-bold-color-word-s-cell-2.html它仅突出显示每个单元格中“brown Fox”的第一个实例:
Sub colorText()
Dim cl As Range
Dim startPos As Integer
Dim totalLen As Integer
Dim searchText As String
' specify text to searh.
searchText = "brown fox"
' loop trough all cells in selection/range
For Each cl In Selection
totalLen = Len(searchText)
startPos = InStr(cl, searchText)
If startPos > 0 Then
With cl.Characters(startPos, totalLen).Font
.FontStyle = "Bold"
.ColorIndex = 3
End With
End If
Next cl
End Sub
我想编辑这个宏,以便它突出显示“棕色狐狸”的每个实例,而不仅仅是第一个实例。作为尝试,我尝试了以下操作:
Sub colorText()
Dim cl As Range
Dim startPos As Integer
Dim totalLen As Integer
Dim searchText As String
Dim endPos As Integer
Dim testPos As Integer
' specify text to search.
searchText = "brown fox"
' loop trough all cells in selection/range
For Each cl In Selection
totalLen = Len(searchText)
startPos = InStr(cl, searchText)
testPos = 0
Do While startPos > testPos
With cl.Characters(startPos, totalLen).Font
.FontStyle = "Bold"
.ColorIndex = 3
End With
endPos = startPos + totalLen
testPos = testPos + endPos
startPos = InStr(testPos, searchText)
Loop
Next cl
End Sub
然而,这仍然只格式化“brown Fox”的第一个实例。
任何想法/编辑将不胜感激。
你的错误在于你的逻辑。您应该更正代码,如下所示:
startPos = InStr(testPos, cl, searchText, vbTextCompare)
而不是这样做:
startPos = InStr(testPos, searchText)
在第二个子中。你现在看到了吗? :-)
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)