Thanks again Dave for helping me out, I will look at your code with
great interest!

Thank you again for taking time! I am learning a lot from you.



On Aug 12, 10:34*am, Dave Peterson wrote:
So you're filtering A:AH and sorting A:AH, but shading A:AI?

I would think that if you had data in AI that you would want to filter and sort
that so that it would be associated with the correct row after the sort.

But I think that this does what you asked:

Option Explicit
Sub testme()
* * *Dim wks As Worksheet
* * *Dim lr As Long

* * *Set wks = Worksheets("Sheet1")

* * *With wks

* * * * *'remove any existing arrows and filters!
* * * * *.AutoFilterMode = False

* * * * *'however you set this lr variable
* * * * *lr = .Cells(.Rows.Count, "A").End(xlUp).Row

* * * * *With .Range("A6:AH" & lr)
* * * * * * *.AutoFilter Field:=29, Criteria1:="=", Operator:=xlAnd
* * * * * * *.AutoFilter Field:=34, Criteria1:="X"
* * * * *End With

* * * * *With .AutoFilter.Range
* * * * * * *If .Columns(1).Cells.SpecialCells(xlCellTypeVisible) _
* * * * * * * * * .Cells.Count = 1 Then
* * * * * * * * *'only the header row is visible, do nothing!
* * * * * * *Else
* * * * * * * * *.Sort _
* * * * * * * * * * *Key1:=.Columns(3), Order1:=xlAscending, _
* * * * * * * * * * *Key2:=.Columns(4), Order2:=xlAscending, _
* * * * * * * * * * *Header:=xlYes, _
* * * * * * * * * * *OrderCustom:=1, _
* * * * * * * * * * *MatchCase:=False, _
* * * * * * * * * * *Orientation:=xlTopToBottom, _
* * * * * * * * * * *DataOption1:=xlSortNormal, _
* * * * * * * * * * *DataOption2:=xlSortNormal

* * * * * * * * *With .Resize(.Rows.Count - 1, .Columns..Count + 1).Offset(1, 0) _
* * * * * * * * * * * * *.Cells.SpecialCells(xlCellTypeVisible).Interior
* * * * * * * * * * *.ColorIndex = 36
* * * * * * * * * * *.Pattern = xlSolid
* * * * * * * * *End With
* * * * * * *End If
* * * * *End With

* * * * *.AutoFilterMode = False

* * *End With

End Sub

If you wanted to filter, sort and shade A:AI, then try this:

Option Explicit
Sub testme2()
* * *Dim wks As Worksheet
* * *Dim lr As Long

* * *Set wks = Worksheets("Sheet1")

* * *With wks

* * * * *'remove any existing arrows and filters!
* * * * *.AutoFilterMode = False

* * * * *'however you set this lr variable
* * * * *lr = .Cells(.Rows.Count, "A").End(xlUp).Row

* * * * *With .Range("A6:AI" & lr)
* * * * * * *.AutoFilter Field:=29, Criteria1:="=", Operator:=xlAnd
* * * * * * *.AutoFilter Field:=34, Criteria1:="X"
* * * * *End With

* * * * *With .AutoFilter.Range
* * * * * * *If .Columns(1).Cells.SpecialCells(xlCellTypeVisible) _
* * * * * * * * * .Cells.Count = 1 Then
* * * * * * * * *'only the header row is visible, do nothing!
* * * * * * *Else
* * * * * * * * *.Sort _
* * * * * * * * * * *Key1:=.Columns(3), Order1:=xlAscending, _
* * * * * * * * * * *Key2:=.Columns(4), Order2:=xlAscending, _
* * * * * * * * * * *Header:=xlYes, _
* * * * * * * * * * *OrderCustom:=1, _
* * * * * * * * * * *MatchCase:=False, _
* * * * * * * * * * *Orientation:=xlTopToBottom, _
* * * * * * * * * * *DataOption1:=xlSortNormal, _
* * * * * * * * * * *DataOption2:=xlSortNormal

* * * * * * * * *With .Resize(.Rows.Count - 1).Offset(1, 0) _
* * * * * * * * * * * * *.Cells.SpecialCells(xlCellTypeVisible).Interior
* * * * * * * * * * *.ColorIndex = 36
* * * * * * * * * * *.Pattern = xlSolid
* * * * * * * * *End With
* * * * * * *End If
* * * * *End With

* * * * *.AutoFilterMode = False

* * *End With

End Sub

=======
The important lines are these:

* * * * *With .AutoFilter.Range
* * * * * * *If .Columns(1).Cells.SpecialCells(xlCellTypeVisible)
* * * * * * * * * .Cells.Count = 1 Then

and
* * * * * * * * *With .Resize(.Rows.Count - 1, .Columns..Count + 1).Offset(1, 0) _
* * * * * * * * * * * * *.Cells.SpecialCells(xlCellTypeVisible).Interior

The first looks at the entire range that was just filtered. *Then it looks at
the first column of that range and counts the visible cells in that column.