我需要一個 VBA 來查找 H 列中在同一單元格中包含“僅”和“可用”一詞的單元格，而忽略所有其他出現的“僅”。然后我想把“only”的字體顏色變成紅色而不改變單元格中其他單詞的顏色。
這是我到目前為止所擁有的。它找到所有出現的“only”，但我不知道如何在同一個單元格中搜索兩個單詞。
Public Sub ChgTxtColor()
Set myRange = Range("H1:H400")
substr = "only"
txtColor = 3
For Each MyString In myRange
lenstr = Len(MyString)
lensubstr = Len(substr)
For i = 1 To lenstr
tempString = Mid(MyString, i, lensubstr)
If tempString = substr Then
MyString.Characters(Start:=i,
Length:=lensubstr).Font.ColorIndex = txtColor
End If
Next i
Next MyString
End Sub
uj5u.com熱心網友回復：
嘗試這個：
Public Sub ChgTxtColor()
Dim myRange As Range, txtColor As Long, c As Range, v
Set myRange = Range("H1:H400")
txtColor = vbRed
For Each c In myRange.Cells 'loop each cell in range
v = c.Value
If InStr(1, v, "only", vbTextCompare) > 0 Then
If InStr(1, v, "available", vbTextCompare) > 0 Then
HilightAllInCell c, "only", txtColor
End If
End If
Next c
End Sub
'hilight all instances of `findText` in range `c` using text color `hiliteColor`
Sub HilightAllInCell(c As Range, findText As String, hiliteColor As Long)
Dim v, pos As Long
v = c.Value
If Len(v) > 0 Then 'any text to check?
pos = 0 'set start position
Do
pos = InStr(pos 1, v, findText, vbTextCompare) 'case-insensitive
If pos > 0 Then 'found?
'using Color instead of ColorIndex is more reproducible
' (since users can edit their color pallette)
c.Characters(Start:=pos, Length:=Len(findText)).Font.Color = hiliteColor
Else
Exit Do 'not found, or no more matches
End If
Loop 'look again
End If 'anything to check
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/438582.html