查找并突出显示一系列单元格中的特定单词 [英] Find and highlight a specific word in a range of cells

问题描述

我想在一系列单元格中找到一个特定的单词，然后将其高亮显示为红色。为此，我创建了这个代码，但它只是在一行上工作，并突出显示了所有单元格文本：

  Sub Find_highlight（）
 Dim ws As Worksheet 
 Dim match As Range 
 Dim findMe As String 
 
设置ws = ThisWorkbook.Sheets（MYSHEET）
 findMe =背景
 
设置match = ws.Range（G3：G1362）Find（findMe）
 match.Font.Color = RGB（255，0，0）
结束Sub 
  

解决方案

让我们说你的excel文件看起来像htis

要对特定字进行颜色，您必须使用单元格的 .Characters 属性。你需要找到这个词从哪里开始，然后着色。

尝试这个

  Option Explicit 
 
 Sub Sample（）
 Dim sPos As Long，sLen As Long 
 Dim aCell As Range 
 Dim ws As Worksheet 
 Dim rng As Range 
 Dim findMe As String 
 
设置ws = ThisWorkbook.Sheets（MYSHEET）
 
设置rng = ws.Range（G3 ：G1362）
 
 findMe =背景
 
用rng 
设置aCell = .Find（what：= findMe，LookIn：= xlValues，_ 
 LookAt：= xlPart，SearchOrder：= xlByRows，SearchDirection：= xlNext，_ 
 MatchCase：= False，SearchFormat：= False）
 
如果不是aCell是没有，然后
 sPos = InStr（1，aCell.Value，findMe）
 sLen = Len（findMe）
 
 aCell.Characters（开始：= sPos，长度：= sLen）.Font.Color = RGB （255，0，0）
如果
结束
结束Sub 
  

OU TPUT

I want to find a specific word in a range of cells then highlight it in red. To do so I created this code but it just worked on one line and highlighted all the cell text:

Sub Find_highlight()
    Dim ws As Worksheet
    Dim match As Range
    Dim findMe As String

    Set ws = ThisWorkbook.Sheets("MYSHEET")
    findMe = "Background"

    Set match = ws.Range("G3:G1362").Find(findMe)
    match.Font.Color = RGB(255, 0, 0)
End Sub

解决方案

Let's say your excel file looks like htis

To color specific word, you have to use the cell's .Characters property. You need to find where does the word start from and then color it.

Try this

Option Explicit

Sub Sample()
    Dim sPos As Long, sLen As Long
    Dim aCell As Range
    Dim ws As Worksheet
    Dim rng As Range
    Dim findMe As String

    Set ws = ThisWorkbook.Sheets("MYSHEET")

    Set rng = ws.Range("G3:G1362")

    findMe = "Background"

    With rng
        Set aCell = .Find(What:=findMe, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

        If Not aCell Is Nothing Then
            sPos = InStr(1, aCell.Value, findMe)
            sLen = Len(findMe)

            aCell.Characters(Start:=sPos, Length:=sLen).Font.Color = RGB(255, 0, 0)
        End If
    End With
End Sub

OUTPUT

这篇关于查找并突出显示一系列单元格中的特定单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！