I have name & address data that I want to format. I can't seem to get this
to work. It stops on the .Header statement with an Error 438
What am I missing, now, please?
Jim Berglund

Option Explicit
Sub SortAndColor()
Dim q, i As Long
With ActiveSheet

Columns("A:G").Select
Selection.Columns.AutoFit
q = .Range("A" & Rows.Count).End(xlUp).Row
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("F1:F" & q), SortOn:=xlSortOnValues,
Order:=xlDescending, _
DataOption:=xlSortNormal
.Sort.SortFields.Add Key:=Range("C1:C" & q), SortOn:=xlSortOnValues,
Order:=xlDescending, _
DataOption:=xlSortNormal
.Sort.SetRange Range("A1:F" & q)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

For i = 1 To q Step 2

.Range(Cells(i, 1), Cells(i, 5)).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -4.99893185216834E-02
.PatternTintAndShade = 0

Next
.Range("A1:G1").Insert Shift:=xlDown
.Range("A1").FormulaR1C1 = "LAST NAME"
.Range("B1").FormulaR1C1 = "FIRST NAME"
.Range("C1").FormulaR1C1 = "ADDRESS"
.Range("D1").FormulaR1C1 = "CITY"
.Range("E1").FormulaR1C1 = "PROV"
.Range("F1").FormulaR1C1 = "POSTAL CODE"
.Range("G1").FormulaR1C1 = "HOME PHONE"
.Rows("1:1").Select
.Selection.Font.Bold = True

.PageSetup.PrintArea = "$A:$G"

End With
End Sub

Hi
Only a quick look but your With syntax has

Activesheet. Header = xlNo

effectively. I would guess your Header parent should be a range?

regards
Paul
On Jun 3, 9:19*pm, "Jim Berglund" wrote:
I have name & address data that I want to format. I can't seem to get this
to work. It stops on the .Header statement with an Error 438
What am I missing, now, please?
Jim Berglund

Option Explicit
Sub SortAndColor()
Dim q, i As Long
* * With ActiveSheet

* * Columns("A:G").Select
* * Selection.Columns.AutoFit
* * * * * * q = .Range("A" & Rows.Count).End(xlUp).Row
* * .Sort.SortFields.Clear
* * .Sort.SortFields.Add Key:=Range("F1:F" & q), SortOn:=xlSortOnValues,
Order:=xlDescending, _
* * * * * * * * * * * * DataOption:=xlSortNormal
* * .Sort.SortFields.Add Key:=Range("C1:C" & q), SortOn:=xlSortOnValues,
Order:=xlDescending, _
* * * * * * * * * * * * DataOption:=xlSortNormal
* * .Sort.SetRange Range("A1:F" & q)
* * * * .Header = xlNo
* * * * .MatchCase = False
* * * * .Orientation = xlTopToBottom
* * * * .SortMethod = xlPinYin
* * * * .Apply

* * For i = 1 To q Step 2

* * * * .Range(Cells(i, 1), Cells(i, 5)).Interior
* * * * * * .Pattern = xlSolid
* * * * * * .PatternColorIndex = xlAutomatic
* * * * * * .ThemeColor = xlThemeColorDark1
* * * * * * .TintAndShade = -4.99893185216834E-02
* * * * * * .PatternTintAndShade = 0

* * Next
* * .Range("A1:G1").Insert Shift:=xlDown
* * .Range("A1").FormulaR1C1 = "LAST NAME"
* * .Range("B1").FormulaR1C1 = "FIRST NAME"
* * .Range("C1").FormulaR1C1 = "ADDRESS"
* * .Range("D1").FormulaR1C1 = "CITY"
* * .Range("E1").FormulaR1C1 = "PROV"
* * .Range("F1").FormulaR1C1 = "POSTAL CODE"
* * .Range("G1").FormulaR1C1 = "HOME PHONE"
* * .Rows("1:1").Select
* * .Selection.Font.Bold = True

* * .PageSetup.PrintArea = "$A:$G"

* * End With
End Sub

Thanks Paul. I have it working, now.
Jim

"Paul Robinson" wrote in message
...
Hi
Only a quick look but your With syntax has

Activesheet. Header = xlNo

effectively. I would guess your Header parent should be a range?

regards
Paul
On Jun 3, 9:19 pm, "Jim Berglund" wrote:
I have name & address data that I want to format. I can't seem to get
this
to work. It stops on the .Header statement with an Error 438
What am I missing, now, please?
Jim Berglund

Option Explicit
Sub SortAndColor()
Dim q, i As Long
With ActiveSheet

Columns("A:G").Select
Selection.Columns.AutoFit
q = .Range("A" & Rows.Count).End(xlUp).Row
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("F1:F" & q), SortOn:=xlSortOnValues,
Order:=xlDescending, _
DataOption:=xlSortNormal
.Sort.SortFields.Add Key:=Range("C1:C" & q), SortOn:=xlSortOnValues,
Order:=xlDescending, _
DataOption:=xlSortNormal
.Sort.SetRange Range("A1:F" & q)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

For i = 1 To q Step 2

.Range(Cells(i, 1), Cells(i, 5)).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -4.99893185216834E-02
.PatternTintAndShade = 0

Next
.Range("A1:G1").Insert Shift:=xlDown
.Range("A1").FormulaR1C1 = "LAST NAME"
.Range("B1").FormulaR1C1 = "FIRST NAME"
.Range("C1").FormulaR1C1 = "ADDRESS"
.Range("D1").FormulaR1C1 = "CITY"
.Range("E1").FormulaR1C1 = "PROV"
.Range("F1").FormulaR1C1 = "POSTAL CODE"
.Range("G1").FormulaR1C1 = "HOME PHONE"
.Rows("1:1").Select
.Selection.Font.Bold = True

.PageSetup.PrintArea = "$A:$G"

End With
End Sub