Macro to Autofilter 2 columns (1 is custom) and fill

[moneytastesbad]

I have a worksheet that I need to do the following to.

1. Autofilter by status column for "Active"

2. Custom Autofilter by Estimated Finish Date for dates less than or equal to the following Sunday's date.

3. Fill the Est Finish Date Column in the remaining rows with green.

4. Repeat the process replacing "Active" with "Ready" and Green with Red

5. Repeat once again with Pending and Blue


I could record the macro manualy, but I dont know how to set it to filter the date column to a date that changes each time you run it.

All help is appreciated.


Travis

 

[Norie]

Travis

Why do you need an autofilter?

Couldn't you do the formatting using conditional formatting?

Or is there more too it?

 

[moneytastesbad]

Not really sure. I was asked by a coworker if I could build that macro.

I have actually came up sith somthing.

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 9/1/2006 by KTCarrow
'
Application.ScreenUpdating = False

'Find Next Sunday's Date

Range("R1").Select
ActiveCell.FormulaR1C1 = "=TODAY()+(7-WEEKDAY(TODAY(),2))"
Range("R1").Select
Selection.NumberFormat = "m/d/yyyy"


Selection.AutoFilter Field:=3, Criteria1:="In Progress"
Range("R1").Select
Selection.Copy
Selection.AutoFilter Field:=10, Criteria1:="<=9/3/2006", Operator:=xlAnd
Columns("J:J").Select
Selection.Interior.ColorIndex = 4

Selection.AutoFilter Field:=10
Selection.AutoFilter Field:=3, Criteria1:="Pending Predecessor"
Range("R1").Select
Selection.Copy
Selection.AutoFilter Field:=10, Criteria1:="<=9/3/2006", Operator:=xlAnd
Columns("J:J").Select
Selection.Interior.ColorIndex = 4

Selection.AutoFilter Field:=10
Selection.AutoFilter Field:=3, Criteria1:="Ready"
Range("F305").Select
Selection.Copy
Selection.AutoFilter Field:=10, Criteria1:="<=9/3/2006", Operator:=xlAnd
Columns("J:J").Select
Selection.Interior.ColorIndex = 4

Selection.AutoFilter Field:=10
Selection.AutoFilter Field:=3
Range("A1").Select

Application.ScreenUpdating = True
End Sub


What do you think?

 

[Norie]

I think this could be done using conditional formatting.

Perhaps you could explain further what you (or your co-worker) are actually trying to achieve?