Getting subscript out of range error - can't figure out why

[FumoSanto]

Hi everyone,

New user, and novice when it comes to vba. What I'm trying to do is change the text color of specific keywords in a spreadsheet. For example, I'd like to highlight the text of 'how to' in the following sentence "I want to know how to change text color", so that it looks like this "I want to know how to change text color".
I came across the following, and it works for only two terms (example, "how to" , "how do I"), but I have a string of about 15 terms I'd like to use, but anything I change over two, I get the subscript out of range error. Any help would be much appreciated! Thanks!

Please Login or Register  to view this content.

[FumoSanto]

Additionally, I changed the "Cat","Mouse" instances in the example above to meet my "How To", "How do I" needs... Just looking to add more terms in that string. Thanks!

[AB33]

Fumo,
Please use code tags with your code as per forum's no 3 rule.

This looks Jindon's code.
In which line does the code errors?
You can add a comma followed by a string in parenthesis, like

Please Login or Register  to view this content.

[FumoSanto]

It is Jindon's code - that is the one I grabbed. I tried adding more in the string with a comma like your example above, but when I go past two, I get the error.

[AB33]

I did not get an error on

Please Login or Register  to view this content.

[FumoSanto]

Very strange. I've tried a couple of times, and still get the same issue. The string I'm trying to use is ("how to","how do I","how can","I want to","need to know","is there a way","need to know","question about","questions about","need a","trying to find","need help","what is","where do I find","how can","how-to"), and it works fine using only the first two in the string, but anything beyond that, it errors out.

[Norie]

Change myColor(x - 1) to either myColor(0) or vbRed.

[FumoSanto]

Thanks Norie, that did the trick!! Now if I can only get it to work on two columns at the same time

[Norie]

That shouldn't be to hard, which columns do you want it to work on and what have you tried?

[FumoSanto]

For example, I'd want to run the keywords on column A and C (but those may shift if I add more data to the spreadsheet). Additionally (and I'm not sure if this is standard behavior), but the text in red does not translate as such to the preview panel (only the text in the cell changes color). Is that standard behavior? Thanks!

[Norie]

If you change the For to this it'll run on columsn A & C.

Please Login or Register  to view this content.

Not sure what you mean about the preview panel, when I view the sheet in Print Preview the prhases are highlighted in red.

[FumoSanto]

Thanks Norie! That is a huge benefit to get the keyword analysis to where I need it. Thank you so much!! Regarding the preview panel, I meant to say the formula bar - please see the attached image. I like to keep the rows set to a height of 15, but sometimes the cells contain a lot of data, and I expand the height of the formula bar to review, but the keywords don't show as red in that space. Any thoughts as to why color doesn't apply there? Thanks!!Red Keyword.jpg

[Norie]

Sorry but formatting won't appear in the formula bar.

[FumoSanto]

Okay thanks, I think this gives me what I need. Again, thanks for all the help!!