Vba code for table formatting

[shoun2502]

Dear Team,




I need outside border of table white and alternate rows having interior color RGB (211,217,,222)


Please help with the following vba code.






Sub All_Tables_All_Sheets_New()
Dim T As ListObject
Dim c As Long
Dim i As Long
For i = 1 To Sheets.Count
For Each T In Sheets(i).ListObjects
With T.DataBodyRange
c = .Columns.Count
.Interior.Color = RGB(234, 237, 239)
.Font.Size = 11
.Name = "Calibri"
.Font.Color = RGB(89, 89, 89)
.Columns(1).Interior.Color = RGB(0, 39, 77)
.Columns(1).Font.Color = vbWhite
.Columns(1).Font.Bold = True
.Rows(1).Interior.Color = RGB(0, 39, 77)
.Rows(1).Font.Color = vbWhite
.Rows(1).Font.Bold = True
.Rows.Borders.LineStyle = xlContinuous
.Rows.Borders.Color = vbWhite
.Rows.Borders.Weight = 3
End With
Next
Next
End Sub




Kindly suggest the same to run the command.


Thank you in anticipation

 

[John_w]

Try this:

Public Sub Format_Tables()

    Dim ws As Worksheet
    Dim table As ListObject
    Dim r As Long
    Dim bd As Variant
    
    For Each ws In ActiveWorkbook.Worksheets
    
        For Each table In ws.ListObjects
            
            For r = 1 To table.DataBodyRange.Rows.Count Step 2
                table.DataBodyRange.Rows(r).Interior.Color = RGB(211, 217, 222)
            Next
            
            For Each bd In Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight)
                With table.Range.Borders(bd)
                    .LineStyle = xlContinuous
                    .ThemeColor = 1
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
            Next
            
        Next
        
    Next
    
End Sub

 

[shoun2502]

Sorry Sir,

But the code is not serving my purpose. I need to add-in the outside border of table white and alternate rows having interior color RGB (211,217,222) in my table .

Keeping the below code intact and subsuming that formatting.

Thanks

 

[shoun2502]

I need a help with the below mentioned code. As the code runs well . Further I need to incorporate the following points.


1. Outside border as Color white
2. In table alternate rows RGB (211 217 222). I meant 1, 3, 5 ROWS etc.
3. 2, 4, 6 rows as .Interior.Color = RGB(234, 237, 239)


Keeping the below formatting intact.


Sub All_Tables_All_Sheets_New()
Dim T As ListObject
Dim c As Long
Dim i As Long
For i = 1 To Sheets.Count
For Each T In Sheets(i).ListObjects
With T.DataBodyRange
c = .Columns.Count
.Interior.Color = RGB(234, 237, 239)
.Font.Size = 11
.Name = "Calibri"
.Font.Color = RGB(89, 89, 89)
.Columns(1).Interior.Color = RGB(0, 39, 77)
.Columns(1).Font.Color = vbWhite
.Columns(1).Font.Bold = True
.Rows(1).Interior.Color = RGB(0, 39, 77)
.Rows(1).Font.Color = vbWhite
.Rows(1).Font.Bold = True
.Rows.Borders.LineStyle = xlContinuous
.Rows.Borders.Color = vbWhite
.Rows.Borders.Weight = 3
End With
Next
Next
End Sub