VBA - format specific characters with strikethrough

Phil Smith

Active Member
Joined
Aug 13, 2004
Messages
285
Office Version
  1. 365
Platform
  1. Mobile
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 strikethrough.

I have a feeling it will probably involve using MID but I am unable to find out the syntax to apply strikethrough to only specific characters in a cell.

For example:
For "165" to have strikethrough, that would apply to the first 3 characters.
For "168", that would be 3 characters starting from the 7th character.
For "170", that would be 3 characters starting from the 13th.
For "172", 3 from the 19th
For 68, 2 from the 25th or 2 from the right.

Your assistance, as always, will be gratefully received. :)
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
The code below assumes that your data is in column A, and I don't know what your criteria actually is. The code below is commented for these assumptions. I imagine you won't want to hard code your criteria, and if that's the case, I can amend the code if you explain the criteria. Either way, this code works on my test data.

Code:
Sub StrikeIt()
Dim r As Range: Set r = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) 'Assuming data is in column A
Dim crit As Variant: crit = Array("168", "172") 'Change for whatever your criteria is
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.Strikethrough = True
        End If
    Next i
Next cel

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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
Back
Top