在excel中，必须在单元格中搜索特定的字符串，并将样式应用于该特定的字符串 [英] In excel have to search for specific string in cell and apply style to that particular string

问题描述

例如：
在excel中，单元格可能会在单元格中包含

团队应该将测试数据加载到文件

我想在此行中只选择一个单词，例如测试，并将样式应用于该特定字符串

团队应该将测试数据加载到文件

我有很多单元格要格式化，所以我想使用VBA

解决方案

这样的东西会将用户选定范围内的所有单元格中的测试更改为粗体。它处理单个单元格中的多个事件

测试不区分大小写

  Option Explicit 
 
 Const strText As String =test
 
 Sub ColSearch_DelRows（）
 Dim rng1 As Range 
 Dim rng2 As Range 
 Dim cel1 As Range 
 Dim cel2 As Range 
 Dim strFirstAddress As String 
 Dim lAppCalc As Long 
 Dim objRegex As Object 
 Dim RegMC As Object 
 Dim RegM As Object 
 
设置objRegex = CreateObject（vbscript.regexp）
 
带objRegex 
 .Global = True 
 .Pattern = strText 
结束
 
'从用户
获取工作范围错误简历Next 
设置rng1 = Application.InputBox（请选择范围搜索& strText， 用户范围选择，Selection.Address（0，0），，，，，8）
 On Error GoTo 0 
如果rng1是Nothing然后退出Sub 
 
与应用程序
 lAppCalc = .Calculation 
 .ScreenUpdating = False 
 .Calculation = xlCalcula b 
 
 $ b设置cel1 = rng1.Find（strText，xlValues，xlPart，xlByRows，，False）
如果不是cel1是没有
设置rng2 = cel1 
 strFirstAddress = cel1.Address 
 Do 
设置cel1 = rng1.FindNext（cel1）
设置rng2 = Union（rng2，cel1）
循环while strFirstAddress< ;> cel1.Address 
如果
 
如果不是rng2是没有
对于每个cel2在rng2 
设置RegMC = objRegex.Execute（cel2.Value）
对于RegMC中的每个RegM 
 cel2.Characters（RegM.firstindex，RegM.Length + 1）.Font.Bold = True 
下一个
下一个
如果
 
应用程序
 .ScreenUpdating = True 
 .Calculation = lAppCalc 
结束
 
 End Sub 
  / pre> 
In excel I have to search for specific word in a cell and replace only that the particular word.

For example:
In excel a cell may contain

  
"team should have loaded test data into the file"
I want to select only a single word in this line such as test and apply a style to that particular string

  
"team should have loaded test data into the file"
I have many cells to format so I would like to use VBA
解决方案
Something like this will change "test" in all cells in a user selected range to bold. It handles multiple occurrences in a single cell

The test is case insensitive
Option Explicit

Const strText As String = "test"

Sub ColSearch_DelRows()
Dim rng1 As Range
Dim rng2 As Range
Dim cel1 As Range
Dim cel2 As Range
Dim strFirstAddress As String
Dim lAppCalc As Long
Dim objRegex As Object
Dim RegMC As Object
Dim RegM As Object

Set objRegex = CreateObject("vbscript.regexp")

With objRegex
    .Global = True
    .Pattern = strText
End With

'Get working range from user
On Error Resume Next
Set rng1 = Application.InputBox("Please select range to search for " & strText, "User range selection", Selection.Address(0, 0), , , , , 8)
On Error GoTo 0
If rng1 Is Nothing Then Exit Sub

With Application
    lAppCalc = .Calculation
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

Set cel1 = rng1.Find(strText, , xlValues, xlPart, xlByRows, , False)
If Not cel1 Is Nothing Then
    Set rng2 = cel1
    strFirstAddress = cel1.Address
    Do
        Set cel1 = rng1.FindNext(cel1)
        Set rng2 = Union(rng2, cel1)
    Loop While strFirstAddress <> cel1.Address
End If

If Not rng2 Is Nothing Then
    For Each cel2 In rng2
        Set RegMC = objRegex.Execute(cel2.Value)
        For Each RegM In RegMC
            cel2.Characters(RegM.firstindex, RegM.Length + 1).Font.Bold = True
        Next
    Next
End If

With Application
    .ScreenUpdating = True
    .Calculation = lAppCalc
End With

End Sub


                        
这篇关于在excel中，必须在单元格中搜索特定的字符串，并将样式应用于该特定的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！