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

Hi Pierre, Tried that but it didn't work. Put the 'End If' after 'End With' and it shows Compile Error: End If without block If. Thanks! — CGG, Jan 09 '18 at 13:00
By the way, if you get into the habit of indenting your code you'll notice things like this right away. I like [this site](http://rubberduckvba.com/indentation). If after you indent your `End Sub` line doesn't make it to the same level of indentation as your `Sub ...()` you'll know something is missing — Marcucciboy2, Jan 09 '18 at 14:17

score 2 · Accepted Answer · answered Jan 09 '18 at 13:02

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

Vityata · Answer 2 · 2018-01-09T13:03:19.067

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

Thanks Vityata! Unfortunately it ony changes the color of the first instance of Sheet 2, not for the whole workbook. — CGG, Jan 09 '18 at 12:52
@CGG - this is because you do not loop through all the worksheets. See this and try to add it to your code. https://stackoverflow.com/questions/25953916/excel-vba-looping-through-multiple-worksheets Make sure that you always open and close Conditions and Loops correclty. — Vityata, Jan 09 '18 at 12:55
I tried that but it repeats itself only on the first instance of sheet 1. So basically only the first time it's shown in sheet 1 and 2 is changed. I'm trying to do it for every time it shows up within a worksheet for all worksheets in a workbook. Thanks! — CGG, Jan 09 '18 at 13:06

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

2 Answers2