Table formatting VBA CODE

[shoun2502]

I am looking for a vba code to have my customized table formatting as I need to execute the same for numerous table and is cumbersome without the code.


The details are mentioned below for the reference.


1. Text : Calibri 10
2. First Column, Row and Last Column Fill colour coding
RGB, Red:49, Green:133, Blue: 156


3 Rest all cells to be color filled
RGB , Red:219, Green:238, Blue:244


4 Text colour to be white & "Bold" for First Column and Row , Last Column


5 Text colour to be black for Remaining Cells




It would be indeed helpful if the forum could address the issue and help me writing the code and subsequently shall surely help me in saving a lot of time.

 

[My Aswer Is This]

Try this:
This assumes you have no more then One table on same sheet.

Sub Format_Table()
Dim c As Long
'Modified  11/14/2018  6:16:46 AM  EST
With ActiveSheet.ListObjects(1).DataBodyRange
c = .Columns.Count
.Interior.Color = RGB(219, 238, 244)
.Font.Size = 10
.Name = "Calibri"
.Font.Color = vbBlack
.Columns(1).Interior.Color = RGB(49, 133, 156)
.Columns(1).Font.Color = vbWhite
.Columns(1).Font.Bold = True
.Columns(c).Interior.Color = RGB(49, 133, 156)
.Columns(c).Font.Color = vbWhite
.Columns(c).Font.Bold = True
.Rows(1).Interior.Color = RGB(49, 133, 156)
.Rows(1).Font.Color = vbWhite
.Rows(1).Font.Bold = True
End With
End Sub

 

[shoun2502]

Thanks for the reply . It works perfectly.

 

[My Aswer Is This]

If you want to format all Tables on all sheets in your workbook the same.
Try this script
It will loop through all sheets in your workbook

Sub All_Tables_All_Sheets_New()
'Modified  11/14/2018  7:32:09 AM  EST
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(219, 238, 244)
            .Font.Size = 10
            .Name = "Calibri"
            .Font.Color = vbBlack
            .Columns(1).Interior.Color = RGB(49, 133, 156)
            .Columns(1).Font.Color = vbWhite
            .Columns(1).Font.Bold = True
            .Columns(c).Interior.Color = RGB(49, 133, 156)
            .Columns(c).Font.Color = vbWhite
            .Columns(c).Font.Bold = True
            .Rows(1).Interior.Color = RGB(49, 133, 156)
            .Rows(1).Font.Color = vbWhite
            .Rows(1).Font.Bold = True
        End With
    Next
Next
End Sub

 

[My Aswer Is This]

Please post all additional needs here in the forum.
Not in PM.

This is a forum Rule.

You asked:

Even I wanted to have inner borders to be white can u please help with the code.

Do you mean all Table borders
Not sure what inner borders means


​

 

[shoun2502]

Thanks for the reply . I am also trying to replicate the last row with the coding

.Rows(c).Interior.Color = RGB(49, 133, 156)
.Rows(c).Font.Color = vbWhite
.Rows(c).Font.Bold = True

but Unfortunately the second last row is being executed. Could you please help with this error.

Thanks in anticipation

 

[My Aswer Is This]

If your using the script for all Tables on all sheets
Here is your added request for Borders

Sub All_Tables_All_Sheets_New()
'Modified  11/14/2018  7:55:14 AM  EST
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(219, 238, 244)
            .Font.Size = 10
            .Name = "Calibri"
            .Font.Color = vbBlack
            .Borders.LineStyle = xlContinuous
            .Borders.Color = vbWhite
            .Columns(1).Interior.Color = RGB(49, 133, 156)
            .Columns(1).Font.Color = vbWhite
            .Columns(1).Font.Bold = True
            .Columns(c).Interior.Color = RGB(49, 133, 156)
            .Columns(c).Font.Color = vbWhite
            .Columns(c).Font.Bold = True
            .Rows(1).Interior.Color = RGB(49, 133, 156)
            .Rows(1).Font.Color = vbWhite
            .Rows(1).Font.Bold = True
        End With
    Next
Next
End Sub

 

[shoun2502]

Similarly, If I have Some data having borders not table Is it possible to execute the code in similar formatting.
With First,Last row and First Column.

Please do help me with that.

 

[RoryA]

Please take a minute to read the forum rules, especially on cross-posting, and then comply with them by adding the relevant links here. Thanks.

 

[My Aswer Is This]

Not sure what your now asking for.
You earlier said all is great.
If your saying you want special formatting for the last row.
Well the last row will always be changing as you add more to your table
So how would we do that?

Thanks for the reply . I am also trying to replicate the last row with the coding

.Rows(c).Interior.Color = RGB(49, 133, 156)
.Rows(c).Font.Color = vbWhite
.Rows(c).Font.Bold = True

but Unfortunately the second last row is being executed. Could you please help with this error.

Thanks in anticipation