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
Bookmarks