Hello All,
I have a problem that is driving me nuts. I am trying to use a VBA script to do three things:
I edited the code to reflect the following:
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!
I have a problem that is driving me nuts. I am trying to use a VBA script to do three things:
- Replace specific text in a string
- Strike through the existing text
- Make both old text and new text red.
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:
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!