如何仅使用VBA格式化单元格中字符串的一部分 [英] How to only format a part of a string in a cell with VBA

问题描述

目标是将这些值A10，B10，C10，D10，E10，F10，G10，H10，I10，J1更改为蓝色字体.

The goal is to change these values A10,B10,C10,D10,E10,F10,G10,H10,I10,J1 to a font blue color.

例如，"Yonge St A10，B10"，只有A10和B10应该使用蓝色字体颜色，而"Yonge St"将保持黑色.

For example, "Yonge St A10, B10", only A10 and B10 should be in a blue font color, whereas "Yonge St" will remain black.

我有一个VBA代码.但是，该代码将更改单元格中所有文本的颜色，而不更改应更改的特定文本.见下文:

I have a VBA code. However, the code changes all the text color in a cell, and not the specific text that should be changed. See below:

Dim i As Long
Dim sequences As String

' The sequence contains the values to highlight
sequences = "A10,B10,C10,D10,E10,F10,G10,H10,I10,J1"

' Split sequence list,  so it can loop through each value in the array

Dim seqList() As String
seqList = Split(sequences, ",")

' This loops through up to Row 20 to determine if the cell value contains a sequence value, if it does, then it highlights it blue
For i = 1 To 20
    Dim cellVal As String
    cellVal = Cells(i, 2).Value 'Cells (i, 2) --> i refers to row number and 2 refers to column number. So in this case I set it to B

    For Each seq In seqList
        Dim outcomeNum As Integer
        outcomeNum = InStr(cellVal, seq)

        If outcomeNum > 0 Then
            Cells(i, 2).Font.Color = RGB(0, 0, 255) ' You can set the blue colour here or change it to something else
        End If

        Next seq

    Next i

推荐答案

您需要在要设置格式的单元格中指定字符的开始和长度:

You need to specify the character start and length within the cell, that you want to format:

因此替换

Cells(i, 2).Font.Color = RGB(0, 0, 255)

使用

Cells(i, 2).Characters(Start:=outcomeNum, Length:=Len(seq)).Font.Color = RGB(0, 0, 255)

---

只是因为我注意到:声明中缺少seq.

Dim seq As Variant

我建议使用 Option Explicit 以避免忘记任何声明并最大程度地减少了由于变量名中的错字引起的错误.

I recommend using Option Explicit to avoid forgetting any declarations and minimizing errors due to typos in variable names.

这篇关于如何仅使用VBA格式化单元格中字符串的一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！