Need help with Script to strikethrough, replace, and color string in a cell

[cscotty]

Hello All,

I have a problem that is driving me nuts. I am trying to use a VBA script to do three things:

1. Replace specific text in a string
2. Strike through the existing text
3. Make both old text and new text red.

I found this post here from a few years ago, and it works great, but I'm having a problem with it.

VBA - format specific characters with strikethrough

Hello Excel expert people! I have a string "165 / 168 / 170 / 172 / 68" that is present in a number of cells throughout my worksheet. Dependant on certain criteria, my aim is to format the text so one or more of either 165, 168, 170, 172 or 68 within those cells are formatted with...

www.mrexcel.com

I edited the code to reflect the following:

Sub ReplaceText()
Dim p As String
Dim q As String
Dim r As Range
Set r = Range("I1:J600" & Range("K" & Rows.Count).End(xlUp).Row)
Dim o As String
    o = InputBox("Enter the name that needs to be replaced:")
        If StrPtr(o) = 0 Then
            Exit Sub
        ElseIf o = vbNullString Then
            Exit Sub
        End If

Dim n As String
    n = InputBox("Enter the new name (Leave blank to only remove the existing name):")
         If StrPtr(n) = 0 Then
            Exit Sub
        End If
        
p = " "
q = o & p & n

r.Replace What:=o, Replacement:=q, MatchCase:=True
Dim crit As Variant: crit = Array(q)
Dim cel As Range
Dim pos As Integer

For Each cel In r
    For i = 0 To UBound(crit)
        pos = InStr(cel.Value, crit(i))
        If pos > 0 Then
            cel.Characters(pos, Len(crit(i))).Font.Color = vbRed
        End If
    Next i
Next cel

crit = Array(o)

For Each cel In r
    For i = 0 To UBound(crit)
        pos = InStr(cel.Value, crit(i))
        If pos > 0 Then
            cel.Characters(pos, Len(crit(i))).Font.Color = vbRed
            cel.Characters(pos, Len(crit(i))).Font.Strikethrough = True
        End If
    Next i
Next cel

End Sub

The code will work as expected when it is first run. However, if I wanted to replace text in a cell that was already changed by the code, the formatting disappears. The red and strikethrough would disappear.

If anyone knows a way I can do this even simpler please let me know.

Thanks in advance!

 

[Dan_W]

Hi. At first I thought you were asking for help with the code, but then I saw the latter half of your message. So you're saying that the code works as expected, but that the issue is when you try and make changes to an already processed/formatted cell?

I might be wrong, but cells that have more than one formatting style applied to them tend to be in something precarious state, and the smallest thing can trigger a loss of formatting in the cell. That being the case, I would first ordinarily make whatever changes to the cell that are needed, and then apply any formatting at the very end.

 

[cscotty]

Perfect, thanks so much!

Hi. At first I thought you were asking for help with the code, but then I saw the latter half of your message. So you're saying that the code works as expected, but that the issue is when you try and make changes to an already processed/formatted cell?

I might be wrong, but cells that have more than one formatting style applied to them tend to be in something precarious state, and the smallest thing can trigger a loss of formatting in the cell. That being the case, I would first ordinarily make whatever changes to the cell that are needed, and then apply any formatting at the very end.

 

[Dan_W]

Well, I'm not sure I was particularly helpful, but I'm glad you've accomplished the desired outcome.