View Full Version : Format only the header data for all the sheets in workbook

07-09-2012, 10:30 PM

I need to format only the headers (row 1 of used columns) of all the sheets in a file using macro. The column in each sheet differs. The formatting should be only for the used columns in the sheets.

The coding which i have formats all the contents in the sheets to grey.


07-09-2012, 10:57 PM
Hi Sindhuja,

Try recording the formatting on a blank/new sheet. Unless I am missing something, this would leave only finding the last column used:)

07-10-2012, 08:26 AM
Hi, sindhuja,

UsedRangeRows(1) should only cover the columns used (and is independant of both start and end column). Example for Excel2007:

Dim ws As Worksheet
For Each ws In Worksheets
With ws.UsedRange.Rows(1).Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
.Bold = True
End With
Next ws Ciao,