I'm an Excel Macros newbie and am trying to create a script that can perform the following:

1. Search the sheet for a value
2. Format the entire row in which the value is situated

The following code works for the first time it finds "Apple", but does not continue to search the sheet. I assume I need a For Next statement, but I can't get it to work. I can't specify the column to search in for "Apple" because it could vary each time.

Dim Fruit As Range
Set Fruit = Cells.Find(what:="Apple", LookAt:=xlWhole)
    If Not Fruit Is Nothing Then
    If Fruit.Row <> Fruit Then
    Fruit.EntireRow.Select
    End If
End If

Selection.Font.Italic = True
With Selection.Font
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0.4
End With

CodePudding user response：

Try this!

    Dim Fruit As Range
Set Fruit = Cells.Find(what:="Apple")
Do While Fruit.Cells.Font.Italic = False
    Fruit.EntireRow.Select
    Selection.Font.Italic = True
    With Selection.Font
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0.4
    End With
    Set Fruit = Cells.Find(after:=Fruit.Cells, what:="Apple", LookAt:=xlWhole, searchdirection:=xlNext)
Loop

I don't think its the optimal solution, but it seems to get the job done.