Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
identify a word in a string then change the format of that word
Using VBA
For each cell in a row, I want to identify a word in a string then change the format of that word. For example, For text in a cell " Id: 00098765 Description: For help with this account number contact XYX Updates: This number is not useful." I want the color of Id, Description, Updates changed to red and bold and moved to a separate line in the same cell. Id: 00098765 Description: For help with this account number contact XYX Updates: This number is not useful." Any help will be appreciated Thank You |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
identify a word in a string then change the format of that word
One way:
Public Sub ColorWords() Dim vWords As Variant Dim rCell As Range Dim i As Long Dim nPos As Long vWords = Array("Id", "Description", "Updates") For Each rCell In Range("A1").Resize( _ 1, Range("IV1").End(xlToLeft).Column) With rCell Application.EnableEvents = False For i = LBound(vWords) To UBound(vWords) nPos = InStr(1, .Text, vWords(i)) If nPos 1 Then _ .Value = Application.Substitute(.Text, _ vWords(i), vbLf & vWords(i)) Next i Application.EnableEvents = True For i = LBound(vWords) To UBound(vWords) nPos = InStr(1, .Text, vWords(i)) If nPos 0 Then With .Characters(nPos, Len(vWords(i))).Font .Bold = True .ColorIndex = 3 End With End If Next i End With Next rCell End Sub In article .com, "EazyExcel" wrote: Using VBA For each cell in a row, I want to identify a word in a string then change the format of that word. For example, For text in a cell " Id: 00098765 Description: For help with this account number contact XYX Updates: This number is not useful." I want the color of Id, Description, Updates changed to red and bold and moved to a separate line in the same cell. Id: 00098765 Description: For help with this account number contact XYX Updates: This number is not useful." Any help will be appreciated |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
identify a word in a string then change the format of that word
JE McGimpsey,
Thnak you. This was vey helpful Thanks once again JE McGimpsey wrote: One way: Public Sub ColorWords() Dim vWords As Variant Dim rCell As Range Dim i As Long Dim nPos As Long vWords = Array("Id", "Description", "Updates") For Each rCell In Range("A1").Resize( _ 1, Range("IV1").End(xlToLeft).Column) With rCell Application.EnableEvents = False For i = LBound(vWords) To UBound(vWords) nPos = InStr(1, .Text, vWords(i)) If nPos 1 Then _ .Value = Application.Substitute(.Text, _ vWords(i), vbLf & vWords(i)) Next i Application.EnableEvents = True For i = LBound(vWords) To UBound(vWords) nPos = InStr(1, .Text, vWords(i)) If nPos 0 Then With .Characters(nPos, Len(vWords(i))).Font .Bold = True .ColorIndex = 3 End With End If Next i End With Next rCell End Sub In article .com, "EazyExcel" wrote: Using VBA For each cell in a row, I want to identify a word in a string then change the format of that word. For example, For text in a cell " Id: 00098765 Description: For help with this account number contact XYX Updates: This number is not useful." I want the color of Id, Description, Updates changed to red and bold and moved to a separate line in the same cell. Id: 00098765 Description: For help with this account number contact XYX Updates: This number is not useful." Any help will be appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change format for an individual word in a cell | Excel Discussion (Misc queries) | |||
Add Word and change format | Excel Discussion (Misc queries) | |||
Conditional format (word change) | Excel Worksheet Functions | |||
Embedded word doc changed to image-need to change back to word. | Excel Discussion (Misc queries) | |||
Change format of single word in cell | Excel Programming |