vba - 使用VBA根据句子的起始字符改变文本的颜色

Question

我正在尝试使用代码有条件地格式化一个单元格中的多行文本，以便如果它以 # 开头，则它保持黑色，如果它以 ~ 开头code> 它将变为红色，如果它不是以 # 或 ~ 开头，它将变为粗体。

我可以让前两位工作，但不能让最后一位工作，我不知道该用什么来代替 ？ 我在下面这样，如果该行以除# 或 ~ 变为粗体。

Sub Conditional_Format()

    Dim sString, eStringA, eStringN, eString, x As Double
    Dim count1, count2, sum As Double
    Dim iString As String

    ' Selects the active cell
    Sheets("Sheet1").Select
    Range("C4").Select

    ' Counts the number of entries in the cell
    count1 = Len(ActiveCell.Value) - Len(Replace(ActiveCell.Value, "#", ""))
    count2 = Len(ActiveCell.Value) - Len(Replace(ActiveCell.Value, "~", ""))
    sum = count1 + count2

    ' Sets sString at the start position of the cell
    sString = 1

    ' Main Loop
    For x = 1 To sum

        ' Determins the location of where each symbol next appears
        eStringA = InStr(sString + 1, ActiveCell.Value, "#")
        eStringN = InStr(sString + 1, ActiveCell.Value, "~")

        ' Sets the location of the closest symbol
        If eStringN = 0 Then
            eString = eStringA
        ElseIf eStringA = 0 Then
            eString = eStringN
        ElseIf eStringA > eStringN Then
            eString = eStringN
        Else: eString = eStringA
        End If

        ' Sets the string of text being examined as iString
        If eString = 0 Then
            iString = Mid(ActiveCell.Value, sString)
        Else: iString = Mid(ActiveCell.Value, sString, eString - sString - 1)
        End If

        ' Colours the string based on if a # can be found in the string
        If InStr(iString, "#") <> 0 Then
            ActiveCell.Characters(sString, eString - sString - 1).Font.Color = RGB(0, 0, 0)
            ActiveCell.Characters(sString, eString - sString - 1).Font.Size = 10
        ElseIf InStr(iString, "~") <> 0 Then
            ActiveCell.Characters(sString, eString - sString - 1).Font.Color = RGB(225, 0, 0)
            ActiveCell.Characters(sString, eString - sString - 1).Font.Size = 10


        ElseIf InStr(iString, ?))  Then
            ActiveCell.Characters(sString, eString - sString - 1).Font.Bold = True
            ActiveCell.Characters(sString, eString - sString - 1).Font.Color = RGB(0, 0, 0)
            ActiveCell.Characters(sString, eString - sString - 1).Font.Size = 10
        End If

        ' Moves the start point up for the next item
        sString = eString

    Next x

End Sub

Answer 1

据我了解您的问题，您已成功划分单元格内的“行”，根据需要设置 # 和 ~ 文本并尝试找出答案如何更改单元格内的其他“线”。

为此，只需将 ElseIf 更改为 Else:

  If InStr(iString, "#") <> 0 Then
    ActiveCell.Characters(sString, eString - sString - 1).Font.Color = RGB(0, 0, 0)
    ActiveCell.Characters(sString, eString - sString - 1).Font.Size = 10
  ElseIf InStr(iString, "~") <> 0 Then
    ActiveCell.Characters(sString, eString - sString - 1).Font.Color = RGB(225, 0, 0)
    ActiveCell.Characters(sString, eString - sString - 1).Font.Size = 10
  'vvvv change this line
  '--- Lines starting with any character OTHER than "#" or "~" will fall in here
  Else
    ActiveCell.Characters(sString, eString - sString - 1).Font.Bold = True
    ActiveCell.Characters(sString, eString - sString - 1).Font.Color = RGB(0, 0, 0)
    ActiveCell.Characters(sString, eString - sString - 1).Font.Size = 10
  End If