Excelで特定文字列の文字色を変更する

ちょいメモ。
自分用備忘録なので内容がアレなのはご容赦を。

Excelで、ある列のセル中にある特定文字列だけを色づけする方法について。とりあえずVBAで処理を組んだので自分用メモ。
対象列は「I列」で、文字列中に「< CRLF >」と「< LF >」という文字列が最大5回（最小0回）登場するという前提で、それらの文字を赤色にするという処理。

一発モノで処理したら終わりのコードなので、VBAの開発画面で対象シートにロジックを書いて、関数部分にカーソルを合わせてF5による実行で処理結果を対象シートに反映。

Public Sub ColorCRLF()

    Dim targetstr As String
    Dim areaStart As Integer
    Dim areaEnd As Integer

    For i = 13 To 10413
        
        targetstr = Range("I" & CStr(i)).Characters.Text
        areaStart = InStr(1, targetstr, "<CRLF>")
        If areaStart > 0 Then
            Range("I" & CStr(i)).Characters(Start:=areaStart, Length:=6).Font.ColorIndex = 3
        End If
        
        If areaStart > 0 And areaStart + 1 <= Len(targetstr) Then
            areaStart = InStr(areaStart + 1, targetstr, "<CRLF>")
            If areaStart > 0 Then
                Range("I" & CStr(i)).Characters(Start:=areaStart, Length:=6).Font.ColorIndex = 3
            End If
        End If
        
        If areaStart > 0 And areaStart + 1 <= Len(targetstr) Then
            areaStart = InStr(areaStart + 1, targetstr, "<CRLF>")
            If areaStart > 0 Then
                Range("I" & CStr(i)).Characters(Start:=areaStart, Length:=6).Font.ColorIndex = 3
            End If
        End If
        
        If areaStart > 0 And areaStart + 1 <= Len(targetstr) Then
            areaStart = InStr(areaStart + 1, targetstr, "<CRLF>")
            If areaStart > 0 Then
                Range("I" & CStr(i)).Characters(Start:=areaStart, Length:=6).Font.ColorIndex = 3
            End If
        End If
        
        If areaStart > 0 And areaStart + 1 <= Len(targetstr) Then
            areaStart = InStr(areaStart + 1, targetstr, "<CRLF>")
            If areaStart > 0 Then
                Range("I" & CStr(i)).Characters(Start:=areaStart, Length:=6).Font.ColorIndex = 3
            End If
        End If
        
    Next

End Sub


Public Sub ColorLF()

    Dim targetstr As String
    Dim areaStart As Integer
    Dim areaEnd As Integer

    For i = 13 To 10413
        
        targetstr = Range("I" & CStr(i)).Characters.Text
        areaStart = InStr(1, targetstr, "<LF>")
        If areaStart > 0 Then
            Range("I" & CStr(i)).Characters(Start:=areaStart, Length:=4).Font.ColorIndex = 3
        End If
        
        If areaStart > 0 And areaStart + 1 <= Len(targetstr) Then
            areaStart = InStr(areaStart + 1, targetstr, "<LF>")
            If areaStart > 0 Then
                Range("I" & CStr(i)).Characters(Start:=areaStart, Length:=4).Font.ColorIndex = 3
            End If
        End If
        
        If areaStart > 0 And areaStart + 1 <= Len(targetstr) Then
            areaStart = InStr(areaStart + 1, targetstr, "<LF>")
            If areaStart > 0 Then
                Range("I" & CStr(i)).Characters(Start:=areaStart, Length:=4).Font.ColorIndex = 3
            End If
        End If
        
        If areaStart > 0 And areaStart + 1 <= Len(targetstr) Then
            areaStart = InStr(areaStart + 1, targetstr, "<LF>")
            If areaStart > 0 Then
                Range("I" & CStr(i)).Characters(Start:=areaStart, Length:=4).Font.ColorIndex = 3
            End If
        End If
        
        If areaStart > 0 And areaStart + 1 <= Len(targetstr) Then
            areaStart = InStr(areaStart + 1, targetstr, "<LF>")
            If areaStart > 0 Then
                Range("I" & CStr(i)).Characters(Start:=areaStart, Length:=4).Font.ColorIndex = 3
            End If
        End If
        
       
    Next


End Sub

たった1秒で仕事が片づく Excel自動化の教科書