Excel Macro needs to be optimized (speed up the execution)

Dear All,

Please find attached a macro, which is doing the job correctly, but takes bit longer time to complete. This needs to be optimized to deliver the result swiftly.

Step1: its finding the char present in col a in the word avaialble in col b, if that char is avl in the word then it will color that word in blue.
Step2: Then, do the same for the list in column F but colour it green
Step3: then find the % of characters which are neither green nor blue
Detailed Step3:

I have a list of sentences, some of the characters in the sentences are colored green (colorindex = 10), some are colored blue (colorindex = 5) and some are colored black. I would like to calculate the proportion of each sentence which is colored either blue or green.

I have written a macro which loops through each character in each sentence and determines what color it is, then spits out the proportion of non-black colored characters in that sentence. The problem is that this is very slow, I believe there are more advanced techniques which can be used in VBA such as storing information in an array then spitting it out as an array at the end. I'm not too sure, if someone could may me make this faster it would be much appreciated!

P.S.:

You can see in the sub routine titled "Colouring" there is a bit of code which I have commented out called "perform the counts"
This runs very quickly, but is slightly different from what I want. Hence someone pls help me to solve my problem.


Thanks.

Thanks a lot for the help...

Is any other way to further optimize (improve the execution speed) this code...

Thanks

Mate, we used to process millions of lines... Thanks in advance...

thanks a lot mate.. for your help...