CODEWITHSUNDEEP
excelvbarange
twils0
twils0

Reputation: 2633

Changing Rows Border color in Excel Worksheet

With help, I built a macro to resize tables. When the table is resized to add rows, the borders of the new rows come in the wrong color.

I got to this point. If I can get the rows loop to work, I would make another loop for the columns, to refresh the formatting for both. The bottom row border of the data body (above the totals row) should not change, and the totals row should not be affected at all.

It tells me I cannot use: s.ListRows(x).Borders(xlEdgeTop) - but I need a way to target the table. What should I do?

Set r = Worksheets("Proposed").Range("Proposed[Strategy/Type]")
i = Application.WorksheetFunction.CountIf(r, "Dividend")
i = i + 13
Set s = Worksheets("Dividend").ListObjects("Dividend")
s.Resize Range("$A$12:$J$" & i)
For x = 1 To s.ListRows.Count
    With s.ListRows(x).Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = -0.149906918546098
    .Weight = xlThin
End With
Next x

Upvotes: 1

Views: 1495

Answers (1)

Alexander Bell
Alexander Bell

Reputation: 7918

Pertinent to you case, refer to the solution shown below (for quick prototyping I was using narrow 12...20 Rows(x) range - replace it with your actual upper bound calculated as s.ListRows.Count:

For x = 12 To 20
    With Rows(x)
        With .Borders(xlEdgeTop)
        .Weight = xlThin
        .ColorIndex = xlAutomatic
        .LineStyle = xlContinuous
        End With
    End With
Next x

Better solution is to apply border styling to entire range without looping through the rows, as shown in the following sample:

With Range("A12:J" & 20)
    With .Borders(xlTop)
        .Weight = xlThin
        .ColorIndex = xlAutomatic
        .LineStyle = xlContinuous
        '.ThemeColor = 1
        .TintAndShade = -0.149906918546098
    End With
End With

In both cases, just replace upper bound "20" with your calculated s.ListRows.Count.

Hope this may help.

Upvotes: 2

Related Questions