Color partial text in cell - VBA

khen1013

New Member
Joined
Jun 29, 2018
Messages
3
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!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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?
 
Upvote 0
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).
Code:
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
 
Upvote 0
Hi, the script works at first, but then I get error 1001 application-defined or object-defined error. Any suggestion?
 
Upvote 0
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)
 
Upvote 0
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?
VBA Code:
c.Characters(x, Len(wrd(i))).Font.Color = vbRed
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top