Workbook formatting

Hi Thea

Thanks for getting in touch. There are probably a couple of ways but you probably want a FOR EACH structure like the following:

Dim ws As Worksheet

For Each ws in ActiveWorkbook.Worksheets

<formatting code>

Next ws

This will loop through every sheet in the workbook and perform the actions you specify. You should prefix all the pieces with ws to make sure the code targets them correctly. For example, the following code will loop through every sheet in the book, horizontally aligning all the cells centrally, bolding the first row, make all cells size 18, and making the first column red (try this on a test workbook, not your existing data!):

Sub FormatMyWorkbook()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

ws.Cells.HorizontalAlignment = xlCenter

ws.Rows(1).Font.Bold = True
ws.Cells.Font.Size = 18
ws.Columns(1).Interior.Color = vbRed

Next ws

End Sub

I hope this gives you enough pointers to modify yourself. You can probably record the formatting components you need and adapt accordingly. Let me know if you have any specific formatting you can't figure out.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector