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!

 

[nlhu]

Thanks for the code.

You said earlier "first time I run this". Given that the code is called automatically when the worksheet is activated, I am not sure what is meant by that phrase.

I am also not sure why this would be in a Worksheet_Activate code but then I am not familiar with what is going on in your workbook or how the workbook is being used.

In any case, I have not been able to produce any unusual behaviour from the code.

Thanks again for your response.

I'm not sure how else I could run this function HighlightWordOrPhrase beside placing it inside the Worksheet_Activate. Ideally, I want this to trigger by itself and not need a button to activate HighlightWordOrPhrase.

Right now, even after I disabled the function altogether, the coloring is still remained. Is this normal?

All I want is to color red those keywords in column K automatically (I put a range on that so it doesn't have to run the loop more than needed). I can add a button to trigger this if that is the best option.

 

[Peter_SSs]

Right now, even after I disabled the function altogether, the coloring is still remained. Is this normal?

Yes, just like any formatting that you apply to a worksheet (excluding conditional formatting), it stays the same unless you actually change it.

I can't tell what is the best option for you because I can't replicate the problem that you seem to be having.