CODEWITHSUNDEEP
excelvba
aceminer
aceminer

Reputation: 4295

Script not changing font colour of all matching strings

I have an Excel macro for changing the font colour of some strings. However, it does not change all instances of the chosen string. For example:

A4: asd 
C4: asd
C5: asd
C6: asdf

Only C6 asd is changed. 

Private Sub Cmd_Click()
Dim r as Range
Dim strString$, x&, y$
strString = Range("A4").value
For Each r in Range("C4:C6")
    r.Font.ColorIndex = 1
    For x = 1 To Len(r.Text) - Len(strString) Step 1 
        If Mid(r.Text, x , Len(strString)) = strString Then 
            r.Characters(x, Len(strString)).Font.ColorIndex = 5
            r.Characters(x, Len(strString)).Font.Bold = 1
        End If 
    Next x
Next r
End Sub

Upvotes: 0

Views: 39

Answers (2)

JeffO
JeffO

Reputation: 8043

 For x = 1 To Len(r.Text) - Len(strString) Step 1

For all the other items 'asd', you basically saying: for x = 1 to (3 -3) which is zero.

If you stepped through your code, you'd find that the code under your nested for/next loop never gets executed.

Upvotes: 2

pnuts
pnuts

Reputation: 59475

Seems to be an indexing issue. For a quick fix please try For x = 0 instead of For x = 1 and x+1 instead of x in the next three lines.

Upvotes: 3

Related Questions