Find Text, Apply an outside border to that row, but only for the to Table Rows,

Question

I have a code that will find the Text: "Homemade" in Column, and want to apply an Outside Border to that Row. But how to apply the outside border to only the rows within the Table, not .EntireRow?

Dim Wb As Workbook
Dim Ws As Worksheet
Dim Tbl     As ListObject
Dim Rng     As Range        ' range in which to set the table
Dim Rl      As Long         ' last row
Dim Cl      As Long         ' last column

For Each Ws In ActiveWorkbook.Worksheets
    With Ws
        If .Index <> 1 Then

'Insert Table with the Data starting in Column A3:M
     ' find the last used row in column A
     Rl = .Cells(.Rows.Count, "A").End(xlUp).Row
     ' find the last used column in row 3
     Cl = .Cells(3, .Columns.Count).End(xlToLeft).Column
     ' set the range for the table
     Set Rng = .Range(.Cells(3, "A"), .Cells(Rl, Cl))
     ' convert the range to a table
     Set Tbl = .ListObjects.Add(xlSrcRange, Rng, , xlYes)

Dim Homemade As Range

   Set Homemade = .Range("C:C").Find("HOMEMADE", LookIn:=xlValues, lookat:=xlWhole)

     With Homemade
       .EntireRow.BorderAround , xlThick, -11489280
     End With

   End If
  End With
 Next Ws

You could use `Intersect` - find the intersection of `Homemade.EntireRow` and the table. — BigBen, May 15 '20 at 16:49
Not related to your question, but to your code... it would be easier to read as a whole if the indention was better, such that the items contained within a `With` statement were indented another order than those not contained, same for those within the `For Each` statement.. — Cyril, May 15 '20 at 17:16
If you have a known table width, you can set-up `.range(.cells(homemade.row,1),.cells(homemade.row,7))` for the `.borderaround`. — Cyril, May 15 '20 at 17:20
Assuming table starts from A:G (=7 columns) ou could also resize the found cell range in C:C by: `Homemade.Offset(Columnoffset:=-2).Resize(Columnsize:=7).BorderAround, xlThick, -11489280` where `Columnoffset:=-2` moves 2 cells to the left and `Columnsize:=7` indicates the total range columns count in your ListObject. Even shorter: `Homemade.Offset(0,-2).Resize(1,7).BorderAround , xlThick, -11489280` :-) — T.M., May 15 '20 at 17:41
@T.M. Thank you.. For my understanding, why `.Resize(1,7)`? If I want to keep it in the same row, my first thought would be: `Resize(0,7) to keep it in the same row..? Also the Color did not change. — CDay, May 15 '20 at 17:52
The 1st argument RowSize indicates the number of rows in the new range, therefore: `1`. (If this argument is omitted, the number of rows in the range remains the same, e.g. `Resize(,7)` ... c.f. [Range.Resize](https://learn.microsoft.com/de-de/office/vba/api/excel.range.resize) — T.M., May 15 '20 at 17:58
@Cyril When trying to add the sytax you suggested. It's not highlighting random rows. Do i still use the `With Home` block? I have `With Homemade .Range(.Cells(Homemade.Row, 1), .Cells(Homemade.Row, 13)).BorderAround , xlThick, -11489280 End With` — CDay, May 15 '20 at 18:10
@T.M. thank you. However, the border color does not change. Why is that? — CDay, May 15 '20 at 18:19
See MS Help [Range.BorderAround](https://learn.microsoft.com/de-de/office/vba/api/excel.range.borderaround) ... if you don't care for the argument order it's best to include the arguments variable name, e.g. `..., Color:=-11489280` :-) — T.M., May 16 '20 at 13:20

score 1 · Answer 1 · answered May 15 '20 at 17:50

It's a little tricky -- you need to establish a ListObject for the table, then determine which row your target text is in. The logic steps here are:

Find the target string
Determine if the target string is in the table
Determine the row in the table
Set a range for that row
Define your border for that range

I'm using a very useful function I wrote to determine if a cell (any cell) is in a table. The test code for the example is here:

Option Explicit

Sub test()
    Dim Ws As Worksheet
    Set Ws = Sheet1

    Dim targetText As String
    targetText = "HOMEMADE"

    With Ws
        Dim Homemade As Range
        Set Homemade = .Range("C:C").Find(targetText, LookIn:=xlValues, lookat:=xlWhole)
        If Not Homemade Is Nothing Then
            Dim tblName As String
            tblName = CellInTable(Homemade)
            If tblName <> vbNullString Then
                Dim theTable As ListObject
                Set theTable = .ListObjects(tblName)
                Dim homemadeRow As Long
                homemadeRow = Homemade.Row - theTable.DataBodyRange.Rows(1).Row + 1
                theTable.DataBodyRange.Rows(homemadeRow).BorderAround , _
                                                                 xlThick, -11489280
            Else
                '--- the cell with the targetText is not in a table
            End If
        Else
            '--- didn't find the targetText
        End If
    End With
End Sub

Private Function CellInTable(ByRef thisCell As Range) As String
    '--- from https://stackoverflow.com/a/35996123/4717755
    Dim tableName As String
    tableName = ""
    On Error Resume Next
    tableName = thisCell.ListObject.Name
    CellInTable = tableName
End Function

Sorry, I have a table already created. I updated the Macro above. The table goes from Column A:M, and It will always have "Homemade" in Column C — CDay, May 15 '20 at 18:06
My example is not creating a table, it's determining if the "HOMEMADE" string exists in a cell within a table. If you can guarantee your string will always be in the table, you only have to attach a `ListObject` to the table (as in my example) and calculate the table row number so you can get the range of that table row. — PeterT, May 17 '20 at 15:50

Find Text, Apply an outside border to that row, but only for the to Table Rows,

1 Answers1