VBA Highlight multiple keywords from text strings

[frmadeira]

Hello,

Any help here would be appreciated please.
The included VBA code almost meets the intended purpose, however, I need a solution that highlights all parameters contained between ##, %% or potentially other special characters (special characters included).
For instance, in the cell range B2:B10 we would find something like:

Checked at ##date1## and ##hour1##

or

Left at %%date1%% and %%hour1%%

What is between ## and %% can be anything, so while I can easily highlight ## or %% I need to be able to highlight whatever parameter is between like this:

Checked at %%date1%% and %%hour1%%

Sub HighlightStrings()
'Updateby Extendoffice
Application.ScreenUpdating = False
Dim Rng As Range
Dim cFnd As String
Dim xTmp As String
Dim x As Long
Dim m As Long
Dim y As Long
Dim xFNum As Integer
Dim xArrFnd As Variant
Dim xStr As String
cFnd = InputBox("Please enter the text, separate them by comma:")
If Len(cFnd) < 1 Then Exit Sub
xArrFnd = Split(cFnd, ",")
For Each Rng In Selection
With Rng
For xFNum = 0 To UBound(xArrFnd)
xStr = xArrFnd(xFNum)
y = Len(xStr)
m = UBound(Split(Rng.Value, xStr))
If m > 0 Then
xTmp = ""
For x = 0 To m - 1
xTmp = xTmp & Split(Rng.Value, xStr)(x)
.Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = 3
xTmp = xTmp & xStr
Next
End If
Next xFNum
End With
Next Rng
Application.ScreenUpdating = True
End Sub

Thank you

 

[DanteAmor]

What is between ## and %% can be anything, so while I can easily highlight ## or %% I need to be able to highlight whatever parameter is between like this:

Checked at %%date1%% and %%hour1%%

Try this:

Sub Highlight_Multiple_Keywords()
  Dim c As Range, rng As Range
  Dim i As Long, ini As Long, n As Long
  Dim arr As Variant, ky As Variant
  
  Set rng = Range("B2:B10")
  rng.Font.Color = vbBlack
  arr = Array("##", "%%")
  
  For Each c In rng
    For Each ky In arr
      ini = 0
      For i = 1 To Len(c.Value)
        n = InStr(i, c.Value, ky)
        If n > 0 Then
          If ini = 0 Then
            ini = n
          Else
            c.Characters(ini, n - ini + 2).Font.Color = vbRed
            ini = 0
          End If
          i = n + 1
        End If
      Next i
    Next ky
  Next c
End Sub

 

[frmadeira]

Perfect, it does the job

Thank you very much

Cheers

 

[DanteAmor]

Im glad to help you. Thanks for the feedback.