Sleeper: How to change automatically the format of a range of cells?

[frade]

Hi,

I have several sheets in my file
I would like automatically define for each sheet the header row
of my columns with the following format (only on the first line)

-width=14
- alignement vertical= justify
- wrap text (text format)

directly in the opening of the file..

Thanks a lot!

Fran?ois

[johnske]

Hi Francois,

If I've read you correctly, this should do what you want, it goes in the 'ThisWorkbook' module (but you don't really need to do this every time you open the file - just the once will do)


Option Explicit

Private Sub Workbook_Open()
Dim Sheet As Worksheet
For Each Sheet In Worksheets
Sheet.Activate
With Rows("1:1")
.HorizontalAlignment = xlJustify
.WrapText = True
.Orientation = 90
End With
Cells.ColumnWidth = 14
Next
End Sub

HTH
John :)

[mdmackillop]

You can also do this without code.
1. Select the first worksheet tab, hold down shift and select the last worksheet tab.
2. Select row 1 and apply your formatting
3. Click on any sheet tab to remove the Select All

If you want this for new workbooks, save the file as a template.

[Bob Phillips]

If I've read you correctly, this should do what you want, it goes in the 'ThisWorkbook' module (but you don't really need to do this every time you open the file - just the once will do

I think he is asking to have row 1 to be different widths than the other rows. If so, although some kludging can be acheived, it is not really feasible.


You can also do this without code.

Let's hope he doesn't have 300 worksheets if he goes this way.:*)

[mdmackillop]

2 Sheets or 300 doesn't make a difference. I was not aware that Row 1 could have different column widths (unless merged). Could you post a sample?

[Paleo]

Without merging cells you cant have different column widths as stated by Malcolm.

[frade]

Ok, Thanks for your help