Color partial text in cell - VBA

[khen1013]

I am brand new to this forum and relatively new to VBA so I apologize if this is not the correct way to go about this. I’ve searched all over and couldn’t find clear cut answers (or at least answers that I understood) so I’m hoping this will help.

I’m trying to color part of the text in a cell. If a cell has ML or OZ or MCG in any part of the worksheet, I want it to be changed to red (or any color really). I’ve seen a lot of answers that use LEN and substrings and etc. but I’m confused about the characters(start,length) property. I don’t know in what part of the cell those letters will be or how many letters even. Can someone explain this or direct me to somewhere that can?
Thank you!

 

[JoeMo]

What you want can be done provided the text you want to color is not in a cell containing a formula. If the cells you want to monitor are not formulaic cells, do you want to check every cell in the used range or do you want to specify a narrower range? Do you want the search for your keywords to be case sensitive?

 

[khen1013]

Not formulaic, check every cell in the active worksheet, not case sensitive.

Thank you!

 

[JoeMo]

Here's a macro you can try. In the event that a keyword (word you want in red font) is part of a longer string, for example, your keyword is OZ and a cell contains Ozwald, then the Oz will be red (Ozwald).

Sub HighlightWordOrPhrase()
Dim wrd As Variant
wrd = Array("ML", "OZ", "MCG") 'enter the words or phrases to highlight between the quote marks
Dim c As Range, x As Variant, i As Long
Application.ScreenUpdating = False
For Each c In ActiveSheet.UsedRange
    For i = LBound(wrd) To UBound(wrd)
        x = InStr(1, c.Value, wrd(i), vbTextCompare)
        If x > 0 Then
        Do
            c.Characters(x, Len(wrd(i))).Font.Color = vbRed
            x = InStr(x + Len(wrd(i)) - 1, c.Value, wrd(i), vbTextCompare)
        Loop While x > 0
    End If
    Next i
Next c
Application.ScreenUpdating = True
End Sub

 

[khen1013]

That worked perfectly; I'm so grateful for your help!

Thank you!!

 

[JoeMo]

That worked perfectly; I'm so grateful for your help!

Thank you!!

You are welcome - thanks for the reply.

 

[nlhu]

Hi, the script works at first, but then I get error 1001 application-defined or object-defined error. Any suggestion?

 

[Peter_SSs]

Hi, the script works at first, but then I get error 1001 application-defined or object-defined error. Any suggestion?

Welcome to the MrExcel board!

It would help if you told us which line in the code gave that error and also provided a small set of sample data that you are using it on.
(And also the actual code if you have modified it at all to suit your data and layout)

 

[nlhu]

Welcome to the MrExcel board!

It would help if you told us which line in the code gave that error and also provided a small set of sample data that you are using it on.
(And also the actual code if you have modified it at all to suit your data and layout)

Hello Peter, hitting the debug button point me to this line below. I have a big spreadsheet with about 150 row and the text could appears twice in each row. Could this cause the issue?

c.Characters(x, Len(wrd(i))).Font.Color = vbRed

 

[Peter_SSs]

Could this cause the issue?

It shouldn't.

You did not address these aspects of my previous post.

... and also provided a small set of sample data that you are using it on.
(And also the actual code if you have modified it at all to suit your data and layout)

BTW, sample data best provided with XL2BB so that we can actually test with it.