PDA

View Full Version : shade header row



talytech
10-04-2012, 07:10 AM
I have 30 sheets in 1 workbook. I want to shade the header row grey for each sheet. Each sheet has different ranges. For example: sheet1 has a range of A thru I .. so I want A1:I1 to be shaded grey.

So I guess what I need is to loop thru all the spreadsheets which I'm able to do. But I don't know how to determine the range to be shaded. Below is what I have which formats the entire sheet.

Dim wkrC
For wkrC = 1 To Excel.Worksheets.Count Step 1
Sheets(wkrC).Select
Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

Cells.EntireColumn.AutoFit

Range("A1").Select

Next wkrC


Can someone help me with this?

GarysStudent
10-04-2012, 09:48 AM
How about:

Dim wkrC
For wkrC = 1 To Excel.Worksheets.Count Step 1
Sheets(wkrC).Select
Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
n = Application.WorksheetFunction.CountA(Rows(1))
With Range(Cells(1, 1), Cells(1, n)).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
Cells.EntireColumn.AutoFit
Range("A1").Select

Next wkrC

talytech
10-04-2012, 10:08 AM
THANK YOU SO MUCH!!! :-) That is perfect.