HELP: How to format certain texts enclosed by specific characters

[michaelt1234]

Hi Everyone. I need some help in formatting texts enclosed by certain characters in excel.
Example:

Make texts enclosed by '<' and '>' to bold/red.



The dog is <watching> and barking as he was <reading>.



Watching the <movies> at morning.



Saying <hello> to you.




Make texts string with 'NOTE:' and ends with '.' blue.

I will be going home. NOTE: Please send your results by 8pm.

*Must be done via Macro

[westconn1]

try like

pos = InStr(Range("b19"), "NOTE:")
If pos > 0 Then
pos1 = InStr(pos + 1, Range("b19"), ".")
If pos1 > 0 Then Range("b19").Characters(pos, pos1 - pos).Font.Color = vbBlue
End Ifchange cell to suit
same code will work for first example too, just add font.bold = true

[michaelt1234]

try like

pos = InStr(Range("b19"), "NOTE:")
If pos > 0 Then
pos1 = InStr(pos + 1, Range("b19"), ".")
If pos1 > 0 Then Range("b19").Characters(pos, pos1 - pos).Font.Color = vbBlue
End Ifchange cell to suit
same code will work for first example too, just add font.bold = true

Great! It worked...


Sub test()
For Each cell In ActiveSheet.UsedRange.Cells
pos = InStr(cell, ":")
If pos > 0 And InStr(cell, "NOTE :") = 0 Then
pos1 = Len(cell)
If pos1 > 0 Then cell.Characters(pos, pos1 - pos + 1).Font.Color = vbRed
End If
Next
End Sub


I did these to iterate on all the cells in the worksheet. :) But just wondering, is it possible to modify the code in order to accommodate the scenario wherein there are multiple instances of '<*>' happening in once cell?

e.g.

The <test> is <working>.

[westconn1]

is it possible to modify the code in order to accommodate the scenario wherein there are multiple instances of '<*>'yes it is possible


pos = instr(cel, "<")
do while pos > 0
pos1 = instr(pos +1, cel, ">")
nex = instr(pos + 1, cel. "<")
if pos1 > 0 and (pos1 < nex or nex = 0) then ' i think i have the logic right, to not find unmatched pairs
cel.Characters(pos, pos1 - pos).Font.Color = vbred
cel.Characters(pos, pos1 - pos).Font.bold = true
end if
pos = nex
loop

[michaelt1234]

Hi! code provided is not working :(


pos = instr(cel, "<")
Do While pos > 0
pos1 = instr(pos +1, cell, ">")
nex = instr(pos + 1, cell. "<")
If pos1 > 0 And (pos1 < nex Or nex = 0) Then ' i think i have the logic right, to not find unmatched pairs
cell.Characters(pos, pos1 - pos).Font.Color = vbred
cell.Characters(pos, pos1 - pos).Font.bold = True
End If
pos = nex
Loop

[mdmackillop]

Change
cel. "<" to

cel, "<"
also, to change both brackets

cel.Characters(pos, pos1 - pos + 1).Font.Color = vbRed
cel.Characters(pos, pos1 - pos + 1).Font.Bold = True

[michaelt1234]

Thanks for the reply. Still didn't work. BTW I am trying to turn all strings enclosed by '(single quote) to red and bold.



Set selectedRange = Application.Selection
For Each cell In selectedRange.Cells
pos = InStr(cel, Chr(39))
Do While pos > 0
pos1 = InStr(pos + 1, cel, Chr(39))
nex = InStr(pos + 1, cel, Chr(39))
If pos1 > 0 And (pos1 < nex Or nex = 0) Then ' i think i have the logic right, to not find unmatched pairs
cel.Characters(pos, pos1 - pos).Font.Color = vbRed
cel.Characters(pos, pos1 - pos).Font.Bold = True
End If
pos = nex
Loop
Next

[mdmackillop]

Use Option Explicit and declare all your variables. cell <> cel

For Each cell In selectedRange.Cells
pos = InStr(cel, Chr(39))