Bold part of text after/ before special character in a cell

Question

Following up the above question and looking for further enhancements, is there a way to bold specific text ("price increase") and the characters in-between that text and special character ("~"), and then produce the expected results?

Expected results:

After price increase of ~0.1% vs others in Year 2020:

After ~6% - 7% price increase vs others in Year 2019:

Sub Colors()

    Dim searchTerms As Variant

    searchTerms = Array("price increase")

    Dim searchString As String
    Dim targetString As String
    Dim offSet As Integer
    Dim colToSearch As Integer
    Dim arrayPos, rowNum As Integer

    colToSearch = 6

    For arrayPos = LBound(searchTerms) To UBound(searchTerms)
        For rowNum = 8 To 15

            searchString = Trim(searchTerms(arrayPos))

            offSet = 1

            Dim x As Integer

            If (Not IsError(Cells(rowNum, colToSearch).Value)) Then
            targetString = Cells(rowNum, colToSearch).Value

            x = HilightString(offSet, searchString, rowNum, colToSearch)
            End If

        Next rowNum
    Next arrayPos

End Sub

Function HilightString(offSet As Integer, searchString As String, rowNum As Integer, ingredCol As Integer) As Integer
            Dim x As Integer
            Dim newOffset As Integer
            Dim targetString As String

            ' offSet starts at 1

            targetString = Mid(Cells(rowNum, ingredCol), offSet)

            foundPos = InStr(LCase(targetString), searchString)

            If foundPos > 0 Then

                ' the found position will cause a highlight where it was found in the cell starting at the offset - 1

                Cells(rowNum, ingredCol).Characters(offSet + foundPos - 1, Len(searchString)).Font.Bold= True

                ' increment the offset to found position + 1 + the length of the search string
                newOffset = offSet + foundPos + Len(searchString)

                x = HilightString(newOffset, searchString, rowNum, ingredCol)
            Else
                ' if it's not found, come back out of the recursive call stack
                Exit Function
            End If
End Function

Refer to this: How to highlight selected text within excel

Note that if you `Dim arrayPos, rowNum As Integer` only `rowNum` is `Integer` but `arrayPos` is `Variant`. In VBA you need to specify a type for **every** variable. Also Excel has more rows than `Integer` can handle therefore row/column counts are of type `Long`: `Dim arrayPos As Long, rowNum As Long`. I recommend [always to use Long instead of Integer](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520) as there is no benefit in using `Integer` in VBA at all. — Pᴇʜ, Apr 01 '20 at 06:01
What is the issue with your code? What is wrong? What errors do you get and where? Note that you cannot just throw in your code and ask us to fix it without giving detailed information what is wrong and where you got stuck fixing it. — Pᴇʜ, Apr 01 '20 at 06:03

score 1 · Answer 1 · answered Apr 01 '20 at 06:13

I recommend to solve that with Regular Expressions (see How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops).

For your example you could use a pattern like (~[0-9.% ]+.* )?price increase(.*~[0-9.%]+)? to find the text that you need to highlight.

See https://regex101.com/r/Un2O0e/1

Bold part of text after/ before special character in a cell

1 Answers1