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

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

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