VBA format multiple tables of different sizes

kdsan

New Member
Joined
Apr 17, 2020
Messages
3
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Hello all -

I have an excel worksheet that will contain roughly 27 tables and I'll need to create 5 versions of this worksheet. The output from the tool I'm using is not in the format that's needed. Below is a screenshot of the output I have and the output I need.

Each of the tables in the worksheet will have a different number of rows. I've managed to get the code that bolds the question, centers the columns (from 'Total' to 'None') and highlights the different sections... but I can't figure out the code that bolds the row labels and percentages, or the row labeled 'column name'. I've been using relative references so maybe that's the issue. Not sure.

Hoping someone can help me figure out the bolding. Bonus -- ideally, I'd like to figure out how to iterate through each table. Right now I have to click on each table to run the format code.

Here's the code I have for the format:
VBA Code:
Format Macro
'
' Keyboard Shortcut: Ctrl+Shift+F
'
    ActiveCell.Select
    Selection.Font.Bold = True
    ActiveCell.Offset(2, 1).Range("A1:B1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveCell.Offset(-1, 3).Range("A1:E1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    ActiveCell.Offset(2, 0).Range("A1:E1").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    ActiveCell.Offset(-2, 8).Range("A1:B1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    ActiveCell.Offset(2, 0).Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    ActiveCell.Offset(0, -11).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With

End Sub

Thanks in advance!
 

Attachments

  • Desired.png
    Desired.png
    79.7 KB · Views: 8
  • Desired.png
    Desired.png
    79.7 KB · Views: 7
  • Output.png
    Output.png
    53.5 KB · Views: 7

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Watch MrExcel Video

Forum statistics

Threads
1,129,836
Messages
5,638,628
Members
417,039
Latest member
Sworks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top