excel - 如果单元格中的每一行以特殊字符开头，如何更改其字体？

Question

如果行以“#”和“-”开头，我需要更改多行单元格中每一行的字体。

我的宏更改了特殊字符的第一个实例及其下面的所有内容的字体。
我需要它来检查单元格中的每一行是否以特殊字符（即# 或-）开头，如果为真则更改。

Dim i as Range
Dim POS As Long, Before As Long, After As Long

For Each i in Sheet(1).UsedRange.Columns(2).Cells
    POS = 0
    If InStr(1, i.text, "#") > 0 Then POS = InStr(1, i.text, "#")
    If InStr(1, i.text, "-") > 0 Then POS = InStr(1, i.text, "-")

    If POS > 0 Then
        Before = InStrRev(i.text,chr(10), POS)
        After = InStr(POS, i.text, vbNewline)

        With i.Characters(Start:=Before + 1, Length:=After - (Before + 1).font
            .Name = "Consolas"
            .Size = 12
       End With
    End If
Next i

标签： excelvba

Answer 1

这是使用 split() 单独检查每一行的一种方法：

Sub Tester()

    Dim c As Range, arr, ln, n As Long, pos As Long
    
    For Each c In Sheet1.UsedRange.Columns(2).Cells
        If Len(c.Value) > 0 Then
            arr = Split(c.Value, vbLf) 'get an array of lines
            pos = 1                    'start point
            
            For n = 0 To UBound(arr)   'loop over the lines
                ln = arr(n)
                Debug.Print ln
                ' # must be escaped by wrapping in []
                If Trim(ln) Like "-*" Or Trim(ln) Like "[#]*" Then
                    With c.Characters(Start:=pos, Length:=Len(ln)).Font
                        .Name = "Consolas"
                        .Size = 12
                    End With
                End If
                pos = pos + Len(ln) + 1 'move start point
            Next n
        End If 'has any value
    Next c
        
End Sub