Need something better than 'today()'

[chriss09]

I have a report that I download once a week however could be using it for a few days each week. I recorded some conditional formating but used =today(). Is it possible to change it so it makes the condition based on the day it is ran (monday) so that it doesn't change when I reopen the now saved report on tuesday? or if there is code that will always base the conditions on monday of each week the report is ran/downloaded. the macros i have now are an AddIn file so they are saved locally. also one of the macros prompts users to save so i don't know if that helps (this is one of the first things to ran)

Columns("J:J").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=TODAY()-8", Formula2:="=TODAY()-2"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = -0.249946592608417
    End With
    Selection.FormatConditions(1).StopIfTrue = False

 

[Jerry Sullivan]

Hi chriss09,

One approach would be to have your macro take today's Date value and save it to a cell.
Then have your conditional formatting formula reference that cell address instead of Today().

 

[chriss09]

Thanks JS411! I can do that but I don't want it to print out on the page.

I inserted it into S1

what would i use to select A:R and to the last row of data (using column A as a reference and set as the print area?

I have this code (used for filling down a condition) which should help me from but can't figure out how to modify it for print area

Dim LR As Long
    
    LR = Cells(Rows.Count, "A").End(xlUp).Row
    
    With Range("C2:H2")
        .FillRight
        .AutoFill Destination:=Range("C2:H" & LR), Type:=xlFillDefault

 

[Jerry Sullivan]

You could find examples of how to set print area on this site; however if your print area doesn't need to be changed dynamically, I'd suggest you set that up in your report template and not complicate things by putting the page setup parameters in your code.

There are lots of alternatives to address your issue of fixing the date, but not have it print.

1. Put the date in a hidden cell (hidden row or column).
2. Put the date in another worksheet
3. Put the date in a Workbook Name.

I'd personally opt for the last one so I wouldn't have to think about accidently deleting a hidden cell or extra worksheet.

You could add this to your code to create a Worksheet Name called ReportDate. Then your CF formula could reference that Name instead of Today()

    ActiveWorkbook.Names.Add Name:="ReportDate", _
        RefersToR1C1:=Date

 

[chriss09]

Thanks Jerry! I tried to make it back here asap to let you know that I just added "$A:$R" to the print area portion of the macro and it worked. I thought it would make it print extra pages without data for some reason.

Thanks again for your help

 

[Jerry Sullivan]

Thanks Jerry! I tried to make it back here asap to let you know that I just added "$A:$R" to the print area portion of the macro and it worked. I thought it would make it print extra pages without data for some reason.

Thanks again for your help

That will work too!
Glad I could help.