Results 1 to 2 of 2

Thread: VBA - format specific characters with strikethrough
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2004
    Location
    Heathrow
    Posts
    266
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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 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.
    USING EXCEL (Office 365): Yet another enforced break along the road to learning Excel! You get so far, then another stumbling block trips the unwary student.... Never stop learning and never be afraid to ask for help.

  2. #2
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA - format specific characters with strikethrough

    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
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •