VBA - format specific characters with strikethrough

[Phil Smith]

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.

 

[lrobbo314]

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.

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