CODEWITHSUNDEEP
excelvba
TourEiffel
TourEiffel

Reputation: 4414

Color Word if in String

I am trying to Color the word "Carrière" or "Carriere" If "carri" Exist in Cell.

To do this I Coded this : 

For j = 1 To Len(.Cells(I, "AG"))
            If InStr(j, .Cells(I, "AG"), "carri", vbTextCompare) Then
                .Cells(I, "AG").Interior.ColorIndex = 6
                count = count + 1

                j = InStr(j, .Cells(I, "AG"), "carri", vbTextCompare)


                .Cells(I, "AG").Characters(j, 8).Font.ColorIndex = 3
                Range(I & ":" & I).Rows.Copy
                Sheets("Carriere").Range("A" & lr).PasteSpecial xlPasteValuesAndNumberFormats
                Sheets("Carriere").Range("A" & lr).PasteSpecial xlPasteFormats
                lr = lr + 1

            End If
        Next j

Note that this For Is set in a loop with loop like below

    With ws
        rw = .Range("AG" & .Rows.count).End(xlUp).Row
        For I = 5 To rw
         'HERE IS MY FOR LOOP

        Next I
 End With

What is doing this code :

It firstly check if "carri" exists in cell, If "carri"exists then we will highlight the cell in yellow (working)

Then we would count the number of char before "carri" this variable is called j once we counted it we will color the font in red for the j char (so the first letter of the word "carri" ( c ) and 7 more char. .Cells(I, "AG").Characters(j, 8).Font.ColorIndex = 3

But This is not working : Sometimes this work and sometimes it color something else ( Often the first word of the string)..

Am I doing something wrong here ?

Upvotes: 0

Views: 71

Answers (1)

JvdV
JvdV

Reputation: 75840

Adapted a piece of code I already had, maybe you can use it to draw some ideas from?

enter image description here 

Option Compare Text

Sub ColorTag()

Dim Tags() As String, Tag As Long, X As Long, Y As Long, ChrPos As Long
With ThisWorkbook.Sheets("Sheet1")
    For X = 5 To .Cells(.Rows.Count, "AG").End(xlUp).Row
        Tags() = Split(.Cells(X, "AG"), " ")
        For Tag = LBound(Tags) To UBound(Tags)
            If Tags(Tag) = "carrière" Or Tags(Tag) = "carriere" Then
                .Cells(X, "AG").Interior.ColorIndex = 6
                Y = InStr(1, .Cells(X, "AG"), Tags(Tag))
                For ChrPos = Y To Y + Len(Tags(Tag)) - 1
                    .Cells(X, "AG").Characters(ChrPos, 1).Font.ColorIndex = 3
                Next ChrPos
            End If
        Next Tag
    Next X
End With

End Sub

enter image description here

Upvotes: 3

Related Questions