|Try something like this. I doubt this precisely correct for your requirements but the concept should be valid.|
Criteria1:="<" & Now + 7
The concept is that you are using the "<" as a text string as the first part of the Criteria1 argument and the VBA Now function to pass the current date (plus 7 days) as the actual criteria. The quotes tell VBA that what is contained within is a text string, so if you included the Now + 7 within the quotes it would not be evaluated.
You may need a more complex string as the actual argument but the basic concept is that text must be enclosed in quotes and anything that you want VBA to evaluate must be outside the quotes. Obviously, it all gets concatenated together with the &.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.