【VBA】Excelでセル内の一部分だけ書式ごと置換

Excelの標準機能でセル内の一部分を置換すると、書式が消えて先頭文字の書式に統一されてしまいます。この記事では、VBAを使ってセル内の特定の文字を置換しつつ、元の書式を維持するためのサンプルコードを紹介します。

前提条件

今回のサンプルコードは、「置換元の文字と置換先の文字数が同じであること」を前提としています。文字数が異なる場合は位置のズレ（オフセット）が発生するため、別途対応が必要になります。

サンプルコード

Sub ReplaceWithFormatting()
    Dim Wb As Workbook
    Dim Ws As Worksheet
    Dim r As Range
    Dim tmpR As Range
    Dim SearchStr As String
    Dim RepStr As String
    Dim i As Long
    
    ' 作業元ファイルとシートの指定
    Set Wb = ThisWorkbook
    Set Ws = Wb.Worksheets("Sheet1")
    
    ' 置換元と置換先の設定（※文字数が同じであること）
    SearchStr = "CK040111"
    RepStr = "FF330112"
    
    ' 検索範囲と書式一時保存セルの指定
    Set tmpR = Ws.Range("H1")
    
    For Each r In Ws.Range("A1:F100")
        ' セルに置換元の文字が含まれている場合
        If Not r.Find(SearchStr) Is Nothing Then
            ' 一時セルに元の内容と書式をコピー
            r.Copy tmpR
            
            ' 通常の置換を実行
            r.Value = Replace(r.Value, SearchStr, RepStr)
            
            ' 文字ごとの書式を復元
            For i = 1 To tmpR.Characters.Count
                r.Characters(i, 1).Font.Color = tmpR.Characters(i, 1).Font.Color
                r.Characters(i, 1).Font.Name = tmpR.Characters(i, 1).Font.Name
                r.Characters(i, 1).Font.Size = tmpR.Characters(i, 1).Font.Size
                r.Characters(i, 1).Font.Strikethrough = tmpR.Characters(i, 1).Font.Strikethrough
                r.Characters(i, 1).Font.Superscript = tmpR.Characters(i, 1).Font.Superscript
                r.Characters(i, 1).Font.OutlineFont = tmpR.Characters(i, 1).Font.OutlineFont
            Next i
        End If
    Next r
    
    ' 一時セルをクリア
    tmpR.Clear
End Sub

使い方と注意点

SearchStr に置換元の文字列、RepStr に置換先の文字列を指定します。処理対象の範囲は Ws.Range("A1:F100") となっていますので、必要に応じて変更してください。