How do I change the font color of an InStr search result ?

[dschmitt]

I have a functioning sub that searches a column for a string and colors the first found string in each cell red.
I would like to modify this sub so that it colors instead of the just the first found string in one cell all matched strings in the cell.

To illustrate:
Current: My house is yellow. My car is also yellow.
Goal: 　　　My house is yellow. My car is also yellow.

How do I need to change the sub below:

Please Login or Register  to view this content.

[nathansav]

You are using instr, so you will only find the 1st one, you need to loop the instr, changing the 1 to be the previous instr+length

so say chars 4 to 8 are to be higlighted, you need to do another instr from 9 onwards

You can do this using a do while loop

Please Login or Register  to view this content.

[dschmitt]

I got the concept. How do I get the position of the found string? I need it to adjust the intStartPoint.

[nathansav]

That is what instr does, so you'll need instr + length

Do you understand the code you posted?

[dschmitt]

Yes, I understand the code I posted. I modified it from a code I found online and used it successfully. Excelforum crashed while I was working on the initial question. So, I had to google for an answer. It was after that that I realized that only the first found string is colored. I have used InStr a lot but wasn't aware that it finds the location of the string. I found out by using MsgBox just before I got your response to my followup question. Thanks for your help. I can finish the sub now.

[nathansav]

No worries, Instr means In String.

Glad to help.

[dschmitt]

I know that InStr means In String. I have been using InStr for years but never bothered to find out what the output of InStr is. I always used InStr to get a yes/no answer (if Instr > 0). I assumed that the output is the number of identified strings. Since today I know that this is not the case. Thanks gain for your help. I learned a couple new things.

Below the final answer to my questions:

Please Login or Register  to view this content.

[nathansav]

Glad to be of help.

Enjoy your knowledge