Hi,
Thanks in advance, this site has been invaluable to me!
I am trying to pick out rows after a certain date, then highlight them so
they are plainly visible when looking at the entire spreadsheet. The code
below seems to do this, but when you remove the filter, all of the rows are
highlighted. If I manually go in and sort, then highlight individual rows,
and remove the filter, it works.

Sub MarkNewPlayers()

Dim afterDate As String
Dim myDate As String
Dim wrksMain As Worksheet
Dim lastRow As String
Set wrksMain = Worksheets("PlDetails")
wrksMain.Select

' Message Box opens to enter the Date to use in the file name
myDate = InputBox("Please enter your date in mm/dd/yyyy format:", "What
date do you want to enter?", "mm/dd/yyyy")
' Highlight dates after the date that is entered
If myDate = "" Or Not IsDate(myDate) Then
MsgBox "You did not enter a date.", 48, "Action cancelled."
Exit Sub
Else
afterDate = myDate
End If
' select all cells, then set filter
' Cells.Select
' Selection.AutoFilter
' find last row number
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
' for testing
MsgBox lastRow
' sort spreadsheet on applicaton date - column D
ActiveSheet.Range("$A$1:$AL$" & lastRow).AutoFilter Field:=4,
Criteria1:="" & afterDate, Operator:=xlAnd
With ActiveWorkbook.Worksheets("PlDetails").AutoFilter. Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' set selection to column D row 2
Range("D2").Select
' loop to pick rows to highlight
Do
If ActiveCell.Value = afterDate Then
' Set "RowNum" to the active row, then select row
rowNum = ActiveCell.Row
Range("A" & rowNum & ":AL" & rowNum).Select
' Green Background Fill
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' ActiveWindow.DisplayGridlines = True

' Below changes active cell back to RowNum, Column 3
ActiveCell.Offset(1, 3).Select
' Else moves from active cell down 1 row
Else: ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell)

ActiveSheet.Range("$A$1:$AL$" & lastRow).AutoFilter Field:=4

End Sub
--
JohnM