Forum Discussion
VBA Code Help - Changing Color of Certain text and characters after specified text
So I am partially VBA savvy but this code seems way over my skill level. I am trying to find the text
"ETIC-" in any cell and change it blue; the catch is, that I also want any character that follows this text to turn blue as well, until I hit a second "/". For example, in a cell I will have the text "3 Problems/ETIC-287/2300/more problems here" where I want only "ETIC-287/2300" to turn blue and stop at the second /. The numbers following "ETIC-" change frequently so I cannot add them to the text string. Currently I have the code
Sub Test1()
Dim strString$, x&
Dim rngCell As Range
strString = Range("F7").Value
Application.ScreenUpdating = False
For Each rngCell In Range("D4", Range("D" & Rows.Count).End(xlUp))
With rngCell
.Font.ColorIndex = 1
For x = 1 To Len(.Text) - Len(strString) Step 1
If Mid(.Text, x, Len(strString)) = strString Then .Characters(x, Len(strString)).Font.ColorIndex = 5
Next x
End With
Next rngCell
Application.ScreenUpdating = True
End Sub
But this isolates only a cell reference (F7) where I have inserted "ETIC-"
Any help would be most appreciated!
Hi bakerbaker19
Have attached a revised macro (Test2) which I think should work for every case.
Rich
7 Replies
Rich99 After investigating what was going wrong, I finally figured it out and your code works!....most of the time. I don't know why but sometimes it doesn't highlight the last 1 to 3 characters before the "/". Do you have any idea what would cause this? (Thanks for the code though as it works 95% of the time!)
Hi bakerbaker19
Have attached a revised macro (Test2) which I think should work for every case.
Rich
Unfortunately it does not. I am getting an error of "Run-time error '9': Subscript out of range" and it highlights this row "Out.Characters(x, Len(LArray(1))).Font.ColorIndex = 5 Rich99
