Find text in cell using cells object with-in IF Then statement

Im am trying to find particular text in a cell then format adjacent cells in the same row. In my code below I am trying to search for "*[Tx]*" using Like, however this is formatting all text that contain "T".

Any help much appreciated.

Sub Summary()

' for each cell in column E find [Tx] then format borders
' for each cell in column E find [H] adjacent cell in columns A:A,D:D,F:F,I:I .clear
' Find [Tx] and [H] in column E and clear this text.

   
    Dim i As Long
    Dim LC As Integer
    
    LC = Sh1.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
   
    Set Sh1 = Sheets("Statement of Profit & Loss")
    u1 = Sh1.Range("E" & Rows.Count).End(xlUp).Row

    
    
For i = 5 To u1
    With Sh1.Cells(i, "E")
        If Sh1.Cells(i, "E") = "" Then
            Sh1.Rows(i & ":" & i).Clear
            
             ElseIf Sh1.Cells(i, "E") Like "*[H]*" Then
               .Offset(, -2).Clear
               .Offset(, -4).Clear
               .Offset(, 1).Clear
               .Offset(, 3).Clear
               
               'does not work
            ElseIf Sh1.Cells(i, "E") Like "* [Tx]" Then
                With Sh1.Rows(i & ":" & i).borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                 With Sh1.Rows(i & ":" & i).borders(xlEdgeBottom)
                         .LineStyle = xlContinuous
                          .Weight = xlMedium
                    End With
               
        End If
   
     End With
    
Next i
    
End Sub

Try this:

Sub Summary()
Dim ws As Worksheet:    Set ws = Sheets("Statement of Profit & Loss")
Dim LR As Long
Dim rCell As Range

LR = ws.Range("E" & Rows.Count).End(xlUp).Row

For Each rCell In ws.Range("E1:E" & LR)
    If Not InStr(1, rCell.Value, "[Tx]") = 0 Then
        With rCell.EntireRow.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlThin
        End With
        With rCell.EntireRow.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlMedium
        End With
    ElseIf Not InStr(1, rCell.Value, "[H]") = 0 Then
        rCell.Offset(, -1).Clear
        rCell.Offset(, -4).Clear
        rCell.Offset(, 1).Clear
        rCell.Offset(, 4).Clear
    ElseIf IsEmpty(rCell) Then
        rCell.EntireRow.Clear
    End If
Next rCell

End Sub

Originally Posted by stnkynts

Try this:

Sub Summary()
Dim ws As Worksheet:    Set ws = Sheets("Statement of Profit & Loss")
Dim LR As Long
Dim rCell As Range

LR = ws.Range("E" & Rows.Count).End(xlUp).Row

For Each rCell In ws.Range("E1:E" & LR)
    If Not InStr(1, rCell.Value, "[Tx]") = 0 Then
        With rCell.EntireRow.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlThin
        End With
        With rCell.EntireRow.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlMedium
        End With
    ElseIf Not InStr(1, rCell.Value, "[H]") = 0 Then
        rCell.Offset(, -1).Clear
        rCell.Offset(, -4).Clear
        rCell.Offset(, 1).Clear
        rCell.Offset(, 4).Clear
    ElseIf IsEmpty(rCell) Then
        rCell.EntireRow.Clear
    End If
Next rCell

End Sub

Hi stnkynts,
Thanks for the code this works, could the last column used be include to replace EntireRow for the cell borders formatting, apologies I should have included this is my initial request?

Maybe this:

Sub Summary()
Dim ws As Worksheet:    Set ws = Sheets("Statement of Profit & Loss")
Dim LR As Long, LC As Long
Dim rCell As Range

LR = ws.Range("E" & Rows.Count).End(xlUp).Row
LC = ws.UsedRange.Columns.Count

For Each rCell In ws.Range("E1:E" & LR)
    If Not InStr(1, rCell.Value, "[Tx]") = 0 Then
        With rCell.Resize(1, LC - rCell.Column).Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlThin
        End With
        With rCell.Resize(1, LC - rCell.Column).Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlMedium
        End With
    ElseIf Not InStr(1, rCell.Value, "[H]") = 0 Then
        rCell.Offset(, -1).Clear
        rCell.Offset(, -4).Clear
        rCell.Offset(, 1).Clear
        rCell.Offset(, 4).Clear
    ElseIf IsEmpty(rCell) Then
        rCell.Resize(1, LC - rCell.Column).Clear
    End If
Next rCell

End Sub

This would have been much easier had we had the workbook in the first place.

Sub Summary()
Dim ws As Worksheet:    Set ws = Sheets("Statement of Profit & Loss")
Dim LR As Long, LC As Long
Dim rCell As Range

LR = ws.Range("E" & Rows.Count).End(xlUp).Row
LC = ws.UsedRange.Columns.Count

Application.ScreenUpdating = False

For Each rCell In ws.Range("E5:E" & LR)
    If Not InStr(1, rCell.Value, "[Tx]") = 0 Then
        With rCell.Offset(0, -4).Resize(1, 4)
            .borders(xlEdgeTop).LineStyle = xlContinuous
            .borders(xlEdgeTop).Weight = xlThin
            .borders(xlEdgeBottom).LineStyle = xlContinuous
            .borders(xlEdgeBottom).Weight = xlMedium
        End With
        With rCell.Offset(0, 1).Resize(1, LC - rCell.Column)
            .borders(xlEdgeTop).LineStyle = xlContinuous
            .borders(xlEdgeTop).Weight = xlThin
            .borders(xlEdgeBottom).LineStyle = xlContinuous
            .borders(xlEdgeBottom).Weight = xlMedium
        End With
    ElseIf Not InStr(1, rCell.Value, "[H]") = 0 Then
        rCell.Offset(, -1).ClearContents
        rCell.Offset(, -4).ClearContents
        rCell.Offset(, 1).ClearContents
        rCell.Offset(, 4).ClearContents
    ElseIf IsEmpty(rCell) Then
        rCell.Offset(0, -4).Resize(1, 4).ClearContents
        rCell.Offset(0, 1).Resize(1, LC - rCell.Column).ClearContents
    End If
Next rCell

Application.ScreenUpdating = True

End Sub

Many thanks this is exactly what im after.