excel macro language to Find text in selection, and then select nearby cells

[sfried]

I'm trying to select multiple cells (p61141), find the text "amc". then select the cells to the left, the cell with amc, and the cell to the right. so if "amc" is in p28, then should select o28:q28. Then add a border on top.

That's it. I only need to find the FIRST 'amc' in the p61141.


I will then copy the same code to do the same thing for column T, and column X, and several other columns.


Thanks in advance for any assistance. It is much appreciated.

 

[William Day]

This should work for you.

Sub sfried()

    Dim rFound As Range, Rng As Range
    Dim SearchTerm As Variant

    SearchTerm = "amc"    ' Change what to find here
    Set Rng = Range("P6:P1141")    ' Change Range here

    Set rFound = Rng.Find(What:=SearchTerm, After:=Rng(1, 1), _
                          LookIn:=xlValues, LookAt:=xlWhole, _
                          SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                          MatchCase:=False, SearchFormat:=False)

    If Not rFound Is Nothing Then
        rFound.Offset(, -1).Resize(, 3).Borders(xlEdgeTop).LineStyle = xlContinuous
    Else
        MsgBox SearchTerm & " not found."
    End If

End Sub

Hope this helps.
WD

 

[sfried]

worked great. thanks again.