VBA Code Help - Changing Color of Certain text and characters after specified text

[bakerbaker19]

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!

[#2]

HI @bakerbaker19 

Does this solve the problem?

Rich

[#3]

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 

[#4]

@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!)

[#5]

No idea I'm afraid but can you attach a sample of where it doesn't work

Rich

[#6 · Accepted solution]

Hi @bakerbaker19 

Have attached a revised macro (Test2) which I think should work for every case.

Rich

[#7]

@Rich99 This one works perfectly! Thank you for all of your help!

[#8]

Hi @Rich99,

In my case, can you please show me how to format text in "Italic" and color for the text for all the text starting from the second line in the cell. It is applied for all the text in a specified column. 

Please see the attached file

Many thanks.

KM