After a cell changes colour when a date is near

[Guest]

When the cell changes to a specific colour say a12 goes red when a date is
within 30 days (a12 is a date an assessment is due) can a12 and a1 (which a
persons name is in a1) be put on a different worksheet and the same for each
row so i can have a list of names and assessments on a seperate worksheet to
print off

so it would look like this

A N OTHER FDA 02-JAN-06
A BODY SUMMARY 02-JAN-06

 

[Bernie Deitrick]

You could use a filter to do that. Select your table, then use Data Filter... Auto Filter. Then
use Custom on your date field, and some combination of "Less than or equal to", " Greater than or
equal to" etc. will allow you to only show the rows with the dates that you are interested in,
prior to printing.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thanks for the quick responce
that would be great if it was just me who dealt with the matrix but other
users who aren't to fluent deal with it and i was hoping if there was a
formula, macro etc that could be inserted that would be great and they would
only have to press print

 

[Bernie Deitrick]

You could use a macro like the one below, which would filter the table starting in cell A1, based on
dates in column B, showing values from today until next week (the + 7 part). It will then print
the table. Assing the macro to a button and the user can just press the button to filter and print.

Post back if you need help getting the specific date range that you want.

HTH,
Bernie
MS Excel MVP

Sub FilterAndPrintOneWeek()

Range("A1").CurrentRegion.AutoFilter _
Field:=2, Criteria1:=">=" & Date, Operator:=xlAnd, _
Criteria2:="<=" & Date + 7
ActiveSheet.PrintOut
Range("A1").CurrentRegion.AutoFilter

End Sub