異なる形式のテキストをセルに追加する（Microsoft Excel）

Sub AddFormatedText()

Dim K As Long     Dim lCol As Long     Dim LastRow As Long     Dim sFind As String     Dim sReplace As String     Dim FullCells As Range     Dim c As Range

' Column to work on     lCol = 1

' Text to be replaced in the cell     ' If this variable is empty, then     ' the contents of sReplace are added     ' to the end of the cell     sFind = ""



' Text to replace sFind or to add to the cell     sReplace = "More Text"



' Find last row in column A     LastRow = Cells(Rows.Count, "A").End(xlUp).Row

' Set range to check     Set FullCells = Range(Cells(1, lCol), Cells(LastRow, lCol))

' Start looking through each cell     For Each c In FullCells         K = 0         ' Only check if there is not a formula in the cell         ' and if the cell has some text in it         If Not c.HasFormula And Len(c.Text) > 0 Then             If sFind > "" Then                 ' Replace first found instance of text in sFind                 K = InStr(c.Text, sFind)

If K > 0 Then                     c = Left(c.Text, K - 1) & sReplace & _                       Mid(c.Text, K + Len(sFind))

End If             Else                 K = Len(c.Text)

' Add the text to the cell (plus a space)

c = c & " " & sReplace                 ' Adjust starting position for formatting                 K = K + 2             End If         End If

' Format the added text, if any         If K > 0 Then             With c.Characters(Start:=K, Length:=Len(sReplace)).Font                 .Underline = xlUnderlineStyleSingle                 .Color = vbRed             End With         End If     Next c End Sub