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

cscotty

New Member
Joined
Mar 18, 2021
Messages
5
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.


I edited the code to reflect the following:

VBA Code:
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!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
436
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

cscotty

New Member
Joined
Mar 18, 2021
Messages
5
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

Active Member
Joined
Jul 11, 2018
Messages
436
Office Version
  1. 365
Platform
  1. Windows
Well, I'm not sure I was particularly helpful, but I'm glad you've accomplished the desired outcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,042
Messages
5,639,731
Members
417,108
Latest member
Thein Than

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top