提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何基于其他文本突出显示单元格内的特定文本?

在Excel中,我们可能很容易根据特定的文本突出显示单元格,但是,在此,我想突出显示单元格中的特定文本,以使其突出显示,但不是整个单元格。 这对我们大多数人来说可能是一个麻烦。 本文将讨论在Excel中解决这项工作的一些技巧。

使用VBA代码突出显示多个单元格中的特定文本

使用VBA代码基于其他文本突出显示单元格中的特定文本


箭头蓝色右泡 使用VBA代码突出显示多个单元格中的特定文本


例如,我有一系列的文本字符串,现在,我想突出显示特定的文本“天空“在这些单元格中获得如下截图所示的结果:

doc突出显示特定文本1

要突出显示单元格中的部分文本,下面的VBA代码可以帮助您。

1. 选择您想突出显示特定文本的单元格,然后按住 ALT + F11 键打开 Microsoft Visual Basic for Applications 窗口。

2。 点击 插页 > 模块,并将以下代码粘贴到 模块 窗口。

VBA代码:突出显示单元格中的部分文本:

Sub HighlightStrings()
'Updateby Extendoffice 20160704
Application.ScreenUpdating = False
Dim Rng As Range
Dim cFnd As String
Dim xTmp As String
Dim x As Long
Dim m As Long
Dim y As Long
cFnd = InputBox("Enter the text string to highlight")
y = Len(cFnd)
For Each Rng In Selection
  With Rng
    m = UBound(Split(Rng.Value, cFnd))
    If m > 0 Then
      xTmp = ""
      For x = 0 To m - 1
        xTmp = xTmp & Split(Rng.Value, cFnd)(x)
        .Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = 3
        xTmp = xTmp & cFnd
      Next
    End If
  End With
Next Rng
Application.ScreenUpdating = True
End Sub

3。 然后按 F5 键运行这个代码,并弹出一个提示框提醒你输入你只想突出显示的文本,看截图:

doc突出显示特定文本2

4。 然后点击 OK 按钮,您指定的所有文本仅在单元格内突出显示,请参阅屏幕截图:

doc突出显示特定文本3


箭头蓝色右泡 使用VBA代码基于其他文本突出显示单元格中的特定文本

这是另一种情况,我有两列,第一列包含文本字符串,第二列是特定文本,现在,我需要根据第二列中的特定文本突出显示第一列中的相关文本。

doc突出显示特定文本4

1。 按住 ALT + F11 键打开 Microsoft Visual Basic for Applications 窗口。

2。 点击 插页 > 模块,并将以下代码粘贴到 模块 窗口。

VBA代码:基于其他文本突出显示单元格中的部分文本:

Sub highlight()
'Updateby Extendoffice 20160704
    Dim xStr As String
    Dim xRg As Range
    Dim xTxt As String
    Dim xCell As Range
    Dim xChar As String
    Dim I As Long
    Dim J As Long
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
      xTxt = ActiveWindow.RangeSelection.AddressLocal
    Else
      xTxt = ActiveSheet.UsedRange.AddressLocal
    End If
LInput:
    Set xRg = Application.InputBox("please select the data range:", "Kutools for Excel", xTxt, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    If xRg.Areas.Count > 1 Then
        MsgBox "not support multiple columns"
        GoTo LInput
    End If
    If xRg.Columns.Count <> 2 Then
        MsgBox "the selected range can only contain two columns "
        GoTo LInput
    End If
    For I = 0 To xRg.Rows.Count - 1
        xStr = xRg.Range("B1").Offset(I, 0).Value
        With xRg.Range("A1").Offset(I, 0)
            .Font.ColorIndex = 1
            For J = 1 To Len(.Text)
                If Mid(.Text, J, Len(xStr)) = xStr Then .Characters(J, Len(xStr)).Font.ColorIndex = 3
            Next
        End With
    Next I
End Sub

3。 粘贴代码后,按 F5 键来运行它,一个提示框会弹出提醒你选择数据范围,其中包含文本字符串和特定的文本,你想要突出显示和基于,请参阅截图:

doc突出显示特定文本5

4。 然后点击 OK 按钮,第二列中所有基于第二列特定文本的第一列中的相应文本都被标记为红色,如下图所示:

doc突出显示特定文本6


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    leroy holmes · 1 months ago
    Awesome. thanks
  • To post as a guest, your comment is unpublished.
    Iris Pereyra · 4 months ago
    This was very useful, thanks very much!
  • To post as a guest, your comment is unpublished.
    Darshan Smg · 4 months ago
    Hi,
    Please any one help me. I want to highlight the specific number in same sentence. For ex : " 2 days leave scansion" want to highlight only "2" in sentence.
  • To post as a guest, your comment is unpublished.
    Darshan · 4 months ago
    Hi, anyone help me this. i want to highlight the Specific number in Cell within the same sentence. for Ex : " 2 days leave scansion " in this sentence want to highlight number.
  • To post as a guest, your comment is unpublished.
    vyshal Kumar · 7 months ago
    Hi,
    could anyone help me with the following

    my Cells in Column "G" contain the text from Column Z to AN, not compulsory that Column g contains all the text from Z to AN.

    My work here is to Highlight the text in Column G if it does not available in any of Column Z - AN

    For example : Cell G1 contains (Hello sir I am doing well) but The text "Sir" do not exist in Column "Z1" to "AN1"

    So i need to highlight the text "Sir"
  • To post as a guest, your comment is unpublished.
    Yaniv · 7 months ago
    i get a run-time error '13', type mismatch when i run the script. any suggestions?
    • To post as a guest, your comment is unpublished.
      Simon Proulx · 4 months ago
      I had the same issue; I found that one of my collumns were formulas and it was looking in them which was what triggered the error 13. Selected a range wihtout formula containing the text to highlight and it worked.
  • To post as a guest, your comment is unpublished.
    Madhusudhanan · 8 months ago
    HI
    I am regular visitor of your website. Your website is very useful for me. This code changes the font color. I want to highlight particular words (for example Insert, blank) in yellow background. I want to highlight this. Not just a font colour. Keep up the great work!!! Many thanks.
    • To post as a guest, your comment is unpublished.
      skyyang · 8 months ago
      Hi, Madhusudhanan,
      May be there is no direct way for highlighting part of text in a cell with background color in Excel worksheet.
      If you have other good method, please comment here.
  • To post as a guest, your comment is unpublished.
    Ace · 9 months ago
    Is there a way to remove all those highlighted words?
    • To post as a guest, your comment is unpublished.
      skyyang · 8 months ago
      Hi, Ace,
      To delete the text based on another column cell, the below vba code may help you, please try it.
      Sub DeleteStr()
      Dim xStr As String
      Dim xRg As Range
      Dim xTxt As String
      Dim xCell As Range
      Dim xChar As String
      Dim i As Long
      Dim J As Long
      On Error Resume Next
      If ActiveWindow.RangeSelection.Count > 1 Then
      xTxt = ActiveWindow.RangeSelection.AddressLocal
      Else
      xTxt = ActiveSheet.UsedRange.AddressLocal
      End If
      LInput:
      Set xRg = Application.InputBox("please select the data range:", "Kutools for Excel", xTxt, , , , , 8)
      If xRg Is Nothing Then Exit Sub
      If xRg.Areas.Count > 1 Then
      MsgBox "not support multiple columns"
      GoTo LInput
      End If
      If xRg.Columns.Count <> 2 Then
      MsgBox "the selected range can only contain two columns "
      GoTo LInput
      End If
      For i = 0 To xRg.Rows.Count - 1
      xStr = xRg.Range("B1").Offset(i, 0).Value
      With xRg.Range("A1").Offset(i, 0)
      .Font.ColorIndex = 1
      For J = 1 To Len(.Text)
      If Mid(.Text, J, Len(xStr)) = xStr Then
      .Characters(J, Len(xStr)).Delete
      End If
      Next
      End With
      Next i
      End Sub
  • To post as a guest, your comment is unpublished.
    Arturs · 1 years ago
    Is there a way to make the first VBA code in a way to work with a fixed list of keywords without a promt box? For example, I always want to find and mark keyword1, keyword2 and keyword3 in all text strings also when one string contains more than one of these keywords (all of them should get highlighted in the specific text string). And is it possible to make keyword1 and keyword2 red but the third one just bold?
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Arturs,

      To highlight multiple keywords from a cell, the following vba code can help you:(you should separate the words by semicolon in the popped out input box)

      Sub HighlightStrings()
      Application.ScreenUpdating = False
      Dim Rng As Range
      Dim cFnd As String
      Dim xTmp As String
      Dim x As Long
      Dim m As Long
      Dim y As Long
      Dim xFNum As Integer
      Dim xArrFnd As Variant
      Dim xStr As String
      cFnd = InputBox("Please enter the text, separate them by semicolon:")
      If Len(cFnd) < 1 Then Exit Sub
      xArrFnd = Split(cFnd, ";")
      For Each Rng In Selection
      With Rng
      For xFNum = 0 To UBound(xArrFnd)
      xStr = xArrFnd(xFNum)
      y = Len(xStr)
      m = UBound(Split(Rng.Value, xStr))
      If m > 0 Then
      xTmp = ""
      For x = 0 To m - 1
      xTmp = xTmp & Split(Rng.Value, xStr)(x)
      .Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = 3
      xTmp = xTmp & xStr
      Next
      End If
      Next xFNum
      End With
      Next Rng
      Application.ScreenUpdating = True
      End Sub
      • To post as a guest, your comment is unpublished.
        IvyCY · 10 months ago
        Hello Skyyang! I found there would be an error if running the code - it could not specify the word following a colon. Do you have any suggestion to improve it? Thank you very much!
        • To post as a guest, your comment is unpublished.
          skyyang · 10 months ago
          Hello, IvyCY,
          If you want to specify the words by a colon, you just need to change the semicolon symbol to colon, please apply the following code:

          Sub HighlightStrings()
          Application.ScreenUpdating = False
          Dim Rng As Range
          Dim cFnd As String
          Dim xTmp As String
          Dim x As Long
          Dim m As Long
          Dim y As Long
          Dim xFNum As Integer
          Dim xArrFnd As Variant
          Dim xStr As String
          cFnd = InputBox("Please enter the text, separate them by colon:")
          If Len(cFnd) < 1 Then Exit Sub
          xArrFnd = Split(cFnd, ":")
          For Each Rng In Selection
          With Rng
          For xFNum = 0 To UBound(xArrFnd)
          xStr = xArrFnd(xFNum)
          y = Len(xStr)
          m = UBound(Split(Rng.Value, xStr))
          If m > 0 Then
          xTmp = ""
          For x = 0 To m - 1
          xTmp = xTmp & Split(Rng.Value, xStr)(x)
          .Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = 3
          xTmp = xTmp & xStr
          Next
          End If
          Next xFNum
          End With
          Next Rng
          Application.ScreenUpdating = True
          End Sub
          • To post as a guest, your comment is unpublished.
            IvyCY · 10 months ago
            Thank you for your reply, skyyang!

            But it still didn't work.
            Perhaps my question was quite confusing. An example may make it clearer.
            For example, one of my keywords is "protect". The text in the cells might be displayed as "Our ways to solve the problem: protection and blablabla... " There might be spaces between ":" and "protection" because the formatting of the text is not always the same. Here, the keyword "protection" could not be highlighted by the codes above. That's the problem.

            I've tried several times but failed. I have also considered removing the punctuation in the cells, but it would compromise the comprehension of the texts. So I asked the question. Hope you don't mind.

            Thank you very much!

            Best regards!
  • To post as a guest, your comment is unpublished.
    lolster · 1 years ago
    hi, i noticed that this is case senitive highliter. Where can i change that it would highlite lower and upper casese in deired word
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, lolster,

      In the above two codes, which code do you want to identify case sensitive?
      We will modify the code for you!
      • To post as a guest, your comment is unpublished.
        elaine · 11 months ago
        Hi, I would like to ask the same question to highlight both lower and upper case, in the case of below:

        "find and mark keyword1, keyword2 and keyword3 in all text strings also when one string contains more than one of these keywords (all of them should get highlighted in the specific text string)"
  • To post as a guest, your comment is unpublished.
    Eugene Cloud · 1 years ago
    Thank you for "Highlight A Specific Text Within Multiple Cells With VBA Code" It works great. Would you please explain:
    line 18 xTmp = xTmp & Split(Rng.Value, cFnd)(x)
    Why idoes the "(x)" have to appear where it is? Is the value of x passed to the Split function as a named argument without the name? I would really like to understand this concept.Thank you for your help and this function. I have learned something I didn't know.
  • To post as a guest, your comment is unpublished.
    L · 1 years ago
    how could i get this code to work but have it highlight in a color other than red?
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello,
      If you want to highlight the text with other color, you just need to change the color index number 3 to other color index number in the below script:
      .Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = 3

      Please try it. Thank you!
  • To post as a guest, your comment is unpublished.
    AMIT · 1 years ago
    Can anyone help me for a code to " copy those highlighted word in a different column ".
  • To post as a guest, your comment is unpublished.
    Alex · 1 years ago
    It worked great for me, thank you for sharing this with the world.
  • To post as a guest, your comment is unpublished.
    Angela · 1 years ago
    I get an error and when I debug it highlights this section m = UBound(Split(Rng.Value, cFnd))
  • To post as a guest, your comment is unpublished.
    Nic · 2 years ago
    Thank you!!!
  • To post as a guest, your comment is unpublished.
    W Law · 2 years ago
    I used Excel365. First VBA is not working. Second one cannot highlight correct keywords i.e. not working too
  • To post as a guest, your comment is unpublished.
    Umar · 2 years ago
    I have checked both given codes in excel 2016 but not working.
  • To post as a guest, your comment is unpublished.
    Pavlo · 2 years ago
    Thank you so much for your tip and macros. I can't imagine what I would do without them.