如何将条件格式应用于部分文本字符串的颜色？ [英] How to apply conditional formatting to color partial text string?

问题描述

有没有一种方法可以调整Excel VBA代码以查找和着色找到的文本字符串的特定部分？

Is there a way to adjust the Excel VBA code to find and color a specific part of the text string found?

我正在使用以下代码查找和着色在第V列中突出显示所有带有文本字符串 @ gmail.com和 @ yahoo.com的单元格。在V列中的文本字符串如下：

I am using the following code to find and highlight all cells with text string "@gmail.com" and "@yahoo.com" in column V. The text string in column V is like this:

BBC43555; [e-mail]; 77888857778;电话：[phone]

BBC43555;[e-mail];77888857778;phone:[phone]

由于无法

我只想突出显示找到的特定文本字符串。我将不胜感激一个简单的解决方案，上面有很好的解释，因为我刚刚开始收集VBA的经验。

I would like to highlight only the specific text string found. I would appreciate a simple solution with a good explanation, since I am just starting to collect experience with VBA.

Columns("V").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="@gmail.com", _
    TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
    .Color = -16752384
    .TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 13421823
    .TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="@yahoo.com", _
    TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
    .Color = -16752384
    .TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 13421823
    .TintAndShade = 0
End With

推荐答案

以下是第1列中带有文本（无公式）的数据示例：

Here is an example on data in column 1, with text (no formulas):

Sub test()

Dim CL As Range
Dim POS As Long, Before As Long, After As Long

For Each CL In Sheets(1).UsedRange.Columns(1).Cells
    POS = 0
    If InStr(1, CL.Text, "@gmail.com") > 0 Then POS = InStr(1, CL.Text, "@gmail.com")
    If InStr(1, CL.Text, "@yahoo.com") > 0 Then POS = InStr(1, CL.Text, "@yahoo.com")
    If POS > 0 Then
        Before = InStrRev(CL.Text, ";", POS)
        After = InStr(POS, CL.Text, ";")
        With CL.Characters(Start:=Before + 1, Length:=After - (Before + 1)).Font
            .FontStyle = "Bold"
        End With
    End If
Next CL

End Sub

也许不是最优雅，最防水的解决方案....

Maybe not the most elegant and waterproof solution....

结果：

这篇关于如何将条件格式应用于部分文本字符串的颜色？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！