Hi everyone,

Sorry that I am new in VBA. Is there a way to underline the characters in-between "after" and "vs" words and must contain specific text ("price increase"), and then produce the expected results below?

Expected results:

after price increase of ~0.1% - 0.15% vs others in Year 2020:
~234% after ~6% - 7% price increase vs others in Year 2019: ~7.5% after price increase of ~1% - 9.9% (avg. ~5%) vs previous price released from Oct 2019 to May 2020 and 1st launch in Oct 2019

Prices of 45 stocks were revised after ~4% - 6% price reduction vs previous price released in May 2020 after ~1% - 7% price increase vs 1st launch in Oct 2019 and price reduction of ~1.2% - 2% vs 1st launch in Oct 2019 resulting from adding 6% discount

Thanks for your advice in advance!

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.Underline= 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