find anything between 2 characters and format in vba

Question

i would like to find a text between 2 characters and format that text only example : cell a1 = hello ! this is the test ! sentance i want to change only the part between the exclamation marks.

Sub Macro2()
Dim varFound As Variant, varSearch As Variant
Dim strAddress As String, intPos As Integer
varSearch = "!*!"
Set varFound = Cells.Find(varSearch, LookIn:=xlValues, LookAt:=xlPart)
If Not varFound Is Nothing Then
strAddress = varFound.Address
Do
With varFound
    Do
    intPos = InStr(intPos + 1, .Value, varSearch, vbTextCompare)
    If intPos Then
    .Characters(Start:=intPos, Length:=Len(varSearch)).Font.FontStyle = "Bold"
    .Characters(Start:=intPos, Length:=Len(varSearch)).Font.ColorIndex = 3
    End If
Loop Until intPos = 0
End With


Set varFound = Cells.FindNext(varFound)
Loop While Not varFound Is Nothing And _
varFound.Address <> strAddress
End If
End Sub
`

the best way is to use regular expressions. I invite to see this link : http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops — Venom, Sep 27 '16 at 12:38

score 1 · Accepted Answer · answered Sep 27 '16 at 13:02

Without Regex, using a simple loop over the characters; select the cells you wish to process and run:

Sub poiuyt()
    Dim r As Range, st As String, boo As Boolean
    Dim L As Long, i As Long

    For Each r In Selection
        st = r.Text
        boo = False
        L = Len(st)
        For i = 1 To L
            If Mid(st, i, 1) = "!" Then
                boo = Not boo
            Else
                If boo Then r.Characters(i, 1).Font.Bold = True
            End If
        Next i
    Next r
End Sub

Before:

and after:

Change the code to reflect any character formatting you require.

@ercümenttüzgen I just made the code do what a human would do. — Gary's Student, Oct 03 '16 at 21:09

find anything between 2 characters and format in vba

1 Answers1