fausto2405
04-21-2015, 02:05 AM
Hi everyone in struggling quite a bit with this task and its getting more complicated in vba so please any help would be really appreciated.
I have two columns, Column E contains texts from drug pamphlets describing what they are used for, essentially a list of diseases in between some random text. Column D contains the list of diseases for each corresponding cell in Column E. I need the text from cells E(x) to be formated wherever the diseases from D(x) appear.
I've attached a sample workbook with what the data looks like and what it should look like with a working macro.
I've gotten as far as having a macro that uses an array (stored manually in vba code) and searches for those terms in the selected range and formats the cells in Column E whenever they are found, and it works. The problem is that with this code i'd have to have one million plus values to store in this array and when i try paste that in vba...it crashes (i know but i am a noob and anything goes at this point).
This is the code below
Option Compare Text
Sub colorText()
Dim cl As Range
Dim startPos As Integer
Dim totalLen As Integer
Dim searchText As String
Dim endPos As Integer
Dim testPos As Integer
' add number of aliases in array.
Dim sArray(1 To 3) As String
Dim i As Long
sArray(1) = "Value1"
sArray(2) = "Value2"
sArray(3) = "Value3"
' specify text to search.
For i = 1 To 3
searchText = sArray(i)
' loop trough all cells in selection/range
For Each cl In Selection
totalLen = Len(searchText)
startPos = InStr(cl, searchText)
testPos = 0
Do While startPos > testPos
With cl.Characters(startPos, totalLen).Font
.FontStyle = "Bold"
.ColorIndex = 3
End With
endPos = startPos + totalLen
testPos = testPos + endPos
startPos = InStr(testPos, cl, searchText, vbTextCompare)
Loop
Next cl
Next i
End Sub
I need to fix the above code so that instead of having one array to all the cells in Column E, it should for eg.
Make an array for cell D2 with each line of text within the cell as an array value and match that against E2.
Then make a new array for cell D3 and match that against E3...and so on. How should i change the above code to make it work in that way? Thanks in advance.
I have two columns, Column E contains texts from drug pamphlets describing what they are used for, essentially a list of diseases in between some random text. Column D contains the list of diseases for each corresponding cell in Column E. I need the text from cells E(x) to be formated wherever the diseases from D(x) appear.
I've attached a sample workbook with what the data looks like and what it should look like with a working macro.
I've gotten as far as having a macro that uses an array (stored manually in vba code) and searches for those terms in the selected range and formats the cells in Column E whenever they are found, and it works. The problem is that with this code i'd have to have one million plus values to store in this array and when i try paste that in vba...it crashes (i know but i am a noob and anything goes at this point).
This is the code below
Option Compare Text
Sub colorText()
Dim cl As Range
Dim startPos As Integer
Dim totalLen As Integer
Dim searchText As String
Dim endPos As Integer
Dim testPos As Integer
' add number of aliases in array.
Dim sArray(1 To 3) As String
Dim i As Long
sArray(1) = "Value1"
sArray(2) = "Value2"
sArray(3) = "Value3"
' specify text to search.
For i = 1 To 3
searchText = sArray(i)
' loop trough all cells in selection/range
For Each cl In Selection
totalLen = Len(searchText)
startPos = InStr(cl, searchText)
testPos = 0
Do While startPos > testPos
With cl.Characters(startPos, totalLen).Font
.FontStyle = "Bold"
.ColorIndex = 3
End With
endPos = startPos + totalLen
testPos = testPos + endPos
startPos = InStr(testPos, cl, searchText, vbTextCompare)
Loop
Next cl
Next i
End Sub
I need to fix the above code so that instead of having one array to all the cells in Column E, it should for eg.
Make an array for cell D2 with each line of text within the cell as an array value and match that against E2.
Then make a new array for cell D3 and match that against E3...and so on. How should i change the above code to make it work in that way? Thanks in advance.