View Full Version : HELP: How to format certain texts enclosed by specific characters
michaelt1234
07-17-2014, 10:41 PM
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
07-18-2014, 03:42 AM
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
07-18-2014, 09:11 PM
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
07-19-2014, 12:36 AM
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
09-07-2017, 08:57 PM
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
09-08-2017, 04:26 AM
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
09-08-2017, 04:50 AM
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
09-08-2017, 06:30 AM
Use Option Explicit and declare all your variables. cell <> cel
For Each cell In selectedRange.Cells
pos = InStr(cel, Chr(39))
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.