VBA: Changing specific text in a cell and looping it to all worksheets

Question

Newbie here. Tried coding the following, but it keeps bringing up errors regardless of where I put certain lines or what I change.

Please help? Thanks!!

Sub WorksheetLoop()

Dim WS_Count As Integer
Dim I As Integer
Dim varFound As Variant, varSearch As Variant
Dim strAddress As String, intPos As Integer

WS_Count = ActiveWorkbook.Worksheets.Count

For I = 1 To WS_Count
varSearch = "CUS_ECO_SEC_CD"
Set varFound = Cells.Find(varSearch, LookIn:=xlValues, LookAt:=xlPart)

If Not varFound Is Nothing Then
strAddress = varFound.Address
Do

With varFound
Do
intPos = InStr(intPos + 1, .Value, varSearch, vbTextCompare)
If intPos Then

.Characters(Start:=intPos, Length:=Len(varSearch)).Font.ColorIndex = 4
End If

Loop Until intPos = 0
End With

Next I

End Sub

Answer 1

Ok, this is one step closer to something you are doing:

Sub WorksheetLoop()

    Dim WS_Count As Integer
    Dim I As Integer
    Dim varFound As Variant, varSearch As Variant
    Dim strAddress As String, intPos As Integer

    WS_Count = ActiveWorkbook.Worksheets.Count

    For I = 1 To WS_Count
        varSearch = "CUS_ECO_SEC_CD"
        Set varFound = Cells.Find(varSearch, LookIn:=xlValues, LookAt:=xlPart)

        If Not varFound Is Nothing Then
            strAddress = varFound.Address
            With varFound
                Do
                    intPos = InStr(intPos + 1, .Value, varSearch, vbTextCompare)
                    If intPos Then
                    .Characters(Start:=intPos, Length:=Len(varSearch)).Font.ColorIndex = 4
                    End If
                Loop Until intPos = 0
            End With
        End If
    Next I
End Sub

Note that if you indent the code correctly VBA helps quite a lot. In your case, I have removed one Do and I have added an End If to make it work.

The easiest way to AutoIndent your code is to probably add SmartIndent or to do it here online - http://www.vbindent.com/

Edit - Something like this will allow you to loop through the worksheets:

Sub WorksheetLoop()

    Dim WS_Count As Integer 'consider using Long instead of Integer somewhere in the future
    Dim I As Integer
    Dim varFound As Variant, varSearch As Variant
    Dim strAddress As String, intPos As Integer
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        varSearch = "CUS_ECO_SEC_CD"
        Set varFound = ws.Cells.Find(varSearch, LookIn:=xlValues, LookAt:=xlPart)
        If Not varFound Is Nothing Then
            strAddress = varFound.Address
            With varFound
                Do
                    intPos = InStr(intPos + 1, .Value, varSearch, vbTextCompare)
                    If intPos Then
                    .Characters(Start:=intPos, Length:=Len(varSearch)).Font.ColorIndex = 4
                    End If
                Loop Until intPos = 0
            End With
        End If
    Next ws

End Sub

Answer 2

Do like this.

Sub WorksheetLoop()

    Dim WS_Count As Integer
    Dim I As Integer
    Dim varFound As Range, varSearch As Variant
    Dim strAddress As String, intPos As Integer
    Dim Ws As Worksheet

    'WS_Count = ActiveWorkbook.Worksheets.Count

    For Each Ws In Worksheets
        With Ws
            varSearch = "CUS_ECO_SEC_CD"
            Set varFound = .Cells.Find(varSearch, LookIn:=xlValues, LookAt:=xlPart)

            If Not varFound Is Nothing Then
                strAddress = varFound.Address
                    Do
                        intPos = InStr(varFound.Value, varSearch)
                        If intPos Then
                            varFound.Characters(Start:=intPos, Length:=Len(varSearch)).Font.ColorIndex = 4
                        End If
                        Set varFound = .Cells.FindNext(varFound)
                    Loop Until strAddress = varFound.Address
            End If
        End With
    Next Ws
End Sub