update the code to work in the fastest way possible

[sr1111]

I got the below code while searching in MrExcel.
My request is to update the code to work in the fastest way possible. I have many thousands of rows of data and it is taking hours.
The code should not work in the sheet "Secret code". Please help.

Sub blah2()
    For Each cll In Sheets("USA").UsedRange.Cells
        myPhrase = Trim(cll.Value)
        phraseLength = Len(myPhrase)
        For j = phraseLength To 4 Step -1
            For i = 1 To phraseLength - 3
                myNewPhrase = Trim(Mid(myPhrase, i, j))
                myNewPhraselength = Len(myNewPhrase)
                If myNewPhraselength > 0 Then
                    clr = cll.Font.Color
                    For Each sht In ThisWorkbook.Sheets
                        If sht.Name <> "USA" Then
                            For Each celle In sht.UsedRange
                                x = InStr(1, celle.Value, myNewPhrase, vbTextCompare)
                                If x > 0 Then
                                    celle.Characters(Start:=x, Length:=myNewPhraselength).Font.Color = clr
                                    Do
                                        x = InStr(x + 1, celle.Value, myNewPhrase, vbTextCompare)
                                        If x > 0 Then celle.Characters(Start:=x, Length:=myNewPhraselength).Font.Color = clr
                                    Loop Until x = 0
                                End If
                            Next celle
                        End If
                    Next sht
                End If
            Next i
        Next j
    Next cll
End Sub

 

[sr1111]

Looking for help.

 

[johnnyL]

You have several loops that involve accessing the data. That will slow things down.

Can you supply some test data via XL2BB that we can test with?

 

[sr1111]

You have several loops that involve accessing the data. That will slow things down.

Can you supply some test data via XL2BB that we can test with?

partial color vba 10_6_2022.xlsm
	A	B	C	D	E	F	G
1	soluble , in water	rule	applicable	Chemistry	lets see	how things: go
2	soluble , in water	rule	applicable	Chemistry	lets see	how things: go
3	soluble , in water	rule	applicable	Chemistry	lets see	how things: go
4	soluble , in water	rule	applicable	Chemistry	lets see	how things: go
5
6
7
8
9
USA

partial color vba 10_6_2022.xlsm
	A	B	C	D	E	F
1
2
3		solubility rules are applied here. I have a rule. Applicability of chemistry solution. Applicability of chemistry solution solubility rules are applied here. I have a rule. Applicability of chemistry solution. Applicability of chemistry solution solubility rules are applied here. I have a rule. Applicability of chemistry solution. Applicability of chemistry solution, solubility rules are applied here. I have a rule. Applicability of chemistry solution. Applicability of chemistry solution. solubility rules are applied here. I have a rule. Applicability of chemistry solution. Applicability of chemistry solution
4
5
6
7		solubility rules are applied here. I have a rule. Applicability of chemistry solution. Applicability of chemistry solution solubility rules are applied here. I have a rule. Applicability of chemistry solution. Applicability of chemistry solution solubility rules are applied here. I have a rule. Applicability of chemistry solution. Applicability of chemistry solution, solubility rules are applied here. I have a rule. Applicability of chemistry solution. Applicability of chemistry solution. solubility rules are applied here. I have a rule. Applicability of chemistry solution. Applicability of chemistry solution
8
9
10
11
12
13
14				solubility rules are applied here. I have a rule. Applicability of chemistry solution. Applicability of chemistry solution solubility rules are applied here. I have a rule. Applicability of chemistry solution. Applicability of chemistry solution solubility rules are applied here. I have a rule. Applicability of chemistry solution. Applicability of chemistry solution, solubility rules are applied here. I have a rule. Applicability of chemistry solution. Applicability of chemistry solution. solubility rules are applied here. I have a rule. Applicability of chemistry solution. Applicability of chemistry solution
Sheet1

partial color vba 10_6_2022.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	solubility rules are applied here
2	Applicability of chemistry solution
3	lets see if things: go as per ruler
4	solubility rules are applied here								solubility rules are applied here						solubility rules are applied here
5	Applicability of chemistry solution								Applicability of chemistry solution						Applicability of chemistry solution
6	lets see if things: go as per ruler								lets see if things: go as per ruler						lets see if things: go as per ruler
7
8
9			solubility rules are applied here
10			Applicability of chemistry solution
11			lets see if things: go as per ruler
12
13					solubility rules are applied here
14					Applicability of chemistry solution
15					lets see if things: go as per ruler
16
17
18
19		solubility rules are applied here. I have a rule												solubility rules are applied here
20														Applicability of chemistry solution
21														lets see if things: go as per ruler
22
Sheet3

 

[sr1111]

One can run the above VBA in the below sheets to check the expected results. I have copied the VBA and the demo sheets from the same link.

 

[sr1111]

Looking for help.

 

[johnnyL]

Do you have a lot of blank cells in your data ranges?

 

[sr1111]

There aren't too many. It might appear.

 

[johnnyL]

From what I can see, the for loops are not optimized. They are coloring the same letters multiple times.

For example the first color change happens when your string = "solub", then later on the string = "solu" and it colors again the matches that were "solub" because it contains "solu".

After that the string becomes "olub" and again colors the same "solub" because it contains "olub"

This explains why the script is taking a long time.

 

[sr1111]

Solution?