StefanoP
04-08-2019, 01:05 PM
Hi,
I am looking to solve an issue with my macro.
I would like it to loop through each cell in my search range and:
1. Look at the adjacent cell of the active cell
* Ex: Active Cell = A2 , then Adjacent cell is B2
2. Find the text from B2 in A2
3. Bold & Color the text in A2
A1=
Text (Start)
B1=
Stop Descriptor
A1=
Text (Desired Result)
A2=
Check This text: This is a long random text to ensure my macro is fully functioning and that i can Test That it will function for other cases
B2=
Check This
A2_Modified=
Check This text: This is a long random text to ensure my macro is fully functioning and that I can Test That it will function for other cases.
A3=
Check This text: This is a long random text to ensure my macro is fully functioning and that i can Test That it will function for other cases
B3=
Test That
A3_Modified=
Check This text: This is a long random text to ensure my macro is fully functioning and that I can Test That it will function for other cases.
* The real data will have different starting texts
I have attempted multiple variations, but can not get the proper functionality i am looking for
Current Macro:
***************************************************************************
Sub MakeWordBold()
Dim strSearch As String
Dim arySearch As Variant
Dim searchRng As Range
Dim cel As Range
Dim SpaceText As String
Dim i As Long, ii As Long
Worksheets("Sheet1").Select
Set searchRng = Range("A2:A5")
strSearch = ActiveCell.Offset(0, 1).Value
arySearch = Split(strSearch, ",")
For Each cel In searchRng
With cel
For ii = LBound(arySearch) To UBound(arySearch)
If Len(arySearch(ii)) > 0 Then
i = InStr(cel.Value, arySearch(ii))
While i > 0
SpaceText = Chr(10) & Len(arySearch(ii))
.Characters(i, Len(arySearch(ii))).Font.Bold = True
.Characters(i, Len(arySearch(ii))).Font.Color = -16776961
i = i + 1
i = InStr(i, cel.Value, arySearch(ii))
Wend
End If
Next ii
End With
Next cel
End Sub
***************************************************************************
Thanks for any guidance.
Regards,
Stefano
I am looking to solve an issue with my macro.
I would like it to loop through each cell in my search range and:
1. Look at the adjacent cell of the active cell
* Ex: Active Cell = A2 , then Adjacent cell is B2
2. Find the text from B2 in A2
3. Bold & Color the text in A2
A1=
Text (Start)
B1=
Stop Descriptor
A1=
Text (Desired Result)
A2=
Check This text: This is a long random text to ensure my macro is fully functioning and that i can Test That it will function for other cases
B2=
Check This
A2_Modified=
Check This text: This is a long random text to ensure my macro is fully functioning and that I can Test That it will function for other cases.
A3=
Check This text: This is a long random text to ensure my macro is fully functioning and that i can Test That it will function for other cases
B3=
Test That
A3_Modified=
Check This text: This is a long random text to ensure my macro is fully functioning and that I can Test That it will function for other cases.
* The real data will have different starting texts
I have attempted multiple variations, but can not get the proper functionality i am looking for
Current Macro:
***************************************************************************
Sub MakeWordBold()
Dim strSearch As String
Dim arySearch As Variant
Dim searchRng As Range
Dim cel As Range
Dim SpaceText As String
Dim i As Long, ii As Long
Worksheets("Sheet1").Select
Set searchRng = Range("A2:A5")
strSearch = ActiveCell.Offset(0, 1).Value
arySearch = Split(strSearch, ",")
For Each cel In searchRng
With cel
For ii = LBound(arySearch) To UBound(arySearch)
If Len(arySearch(ii)) > 0 Then
i = InStr(cel.Value, arySearch(ii))
While i > 0
SpaceText = Chr(10) & Len(arySearch(ii))
.Characters(i, Len(arySearch(ii))).Font.Bold = True
.Characters(i, Len(arySearch(ii))).Font.Color = -16776961
i = i + 1
i = InStr(i, cel.Value, arySearch(ii))
Wend
End If
Next ii
End With
Next cel
End Sub
***************************************************************************
Thanks for any guidance.
Regards,
Stefano