I have the following code that I customized after getting help from a forum member. I am trying to add 1 additional criteria using a date test but cannot get it to work. I want the to filter out any rows where column X of each row is only 2 days old. So if the date in column X is 2 days old I don't want it as part of the data shown.
I am pretty sure the issue is with this part of the code but not sure what the right syntax is to achieve the result:
can anyone help me get this working?
See full procedure below:
I am pretty sure the issue is with this part of the code but not sure what the right syntax is to achieve the result:
VBA Code:
.Cells(15, "CU").Value = "<" & Date - 2
can anyone help me get this working?
See full procedure below:
VBA Code:
Sub Filter_by_Red_CD_LE()
ActiveSheet.Unprotect
Dim r As Range
Dim dataRange As Range
Dim criteriaRange As Range
With Worksheets("Pipeline")
' Define the data range.
Set dataRange = .Range("A10:CP500")
'Define criteria range
Set criteriaRange = .Range("CQ10:CU12")
' Copy column headers
.Cells(10, "CQ").Value = .Cells(10, "S").Value
.Cells(10, "CR").Value = .Cells(10, "CK").Value
.Cells(10, "CS").Value = .Cells(10, "AZ").Value
.Cells(10, "CT").Value = .Cells(10, "H").Value
.Cells(10, "CU").Value = .Cells(10, "X").Value
'Define Criteria values
.Cells(11, "CQ").Value = "Red"
.Cells(12, "CR").Value = "Red"
.Cells(13, "CS").Value = "<>" & "*DOC*"
.Cells(14, "CT").Value = "<>" & "*Broker*"
.Cells(15, "CU").Value = "<" & Date - 2
End With
' Apply the filter.
dataRange.AdvancedFilter xlFilterInPlace, criteriaRange
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollRow = 11
Range("A8").Value = "Current Filter = RED LE/CD"
Range("CQ10:CU12").ClearContents
End Sub