change cell fill color after change but only turn on once printed

[Doug Mutzig]

Good afternoon!

I have a table that is used for a schedule with several columns: Role, Name, Service, D1, D2, D3, etc. In the Day columns (D1, D2, etc.) the cells have data validation to show a list of possible codes.

The end user will fill out the schedule and once completed print the schedule to PDF. At this point I would like to have a cell fill with the current date/time of printing and to turn on formatting so that if any of the cells in the Day columns are changed they are filled with a bright orange color (#46 color code).

I have looked around and found some hints on this but they all work on the worksheet from the start which is not what I want to do. The schedule may be changed several times a day prior to being finalized and printed and I do not want those changes to be orange, only after they finalize (i.e. print the schedule for the first time). I am thinking that the formatting could be activated based on the value in a cell that shows the current date/time of printing (i.e. $A$4 > 0)

can this be accomplished with conditional formatting or vba?

Thank you very much for your help on this!

 

[James006]

Hello,

To make your life easier .... just add a Print Button which will handle :

1. Current date and Time

2. Orange formatting

and ...obviously your Print ...

HTH

 

[Doug Mutzig]

Hi James,

Good idea! Thank you!

Followup question is how to get the orange formatting to work? There are a few ways I have seen where you have to have a copy of the worksheet but I do not want to do that. Is there a worksheet event that would work on this as I do not need to compare the old and new values, I only want to fill the cell when changed.

 

[Sajukai]

Could also use VBA - ThisWorkbook > Worksheet > BeforePrint event and use a cell on the worksheet to hide a TRUE/FALSE value. Then use the worksheet change event to check the "Target" via a select case statement is one of the cells you want marked orange afterwards if printed.

Example:

Inside ThisWorkbook:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
      If Cancel = False then Sheet1.Cells(1, 1).Value = True : Sheet1.Cells(1,2).value = format(Now(), "mm/dd/yyyy hh:mm:ss")
End Sub

Inside Sheet to be printed:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    Select Case Target.AddressLocal
    Case "$D$5", "$D$6", "$D$9"
        If Sheet1.Cells(1, 1).Value = True Then ActiveSheet.Range(Target.AddressLocal).Interior.ColorIndex = 46
    End Select
Application.EnableEvents = True
End Sub

 

[Doug Mutzig]

Hi Sajukai,

I seem to be doing something wrong as I cannot get your code to work.

I have the ThisWorkBook code placed correctly and have updated the Sheet to Sheet2 for testing.

If I understand the code before printing it will place "True" in A1, and the current date/time in A2 if the print job isn't canceled. Any ideas on why it would not be working?

I then have the Sheet code placed in my worksheet (called P1) and again adjusted the sheet1 to sheet2.

This code basically looks at sheet 2 cell A1 for True and then changes the interior cell color for cells D5, D6, and D9

Am I correct in this?

Question: can the code be modified to work on SaveAs, but not Save? And can I enter a range of cells for the Target.AddressLocal Case (such as $G$16:$AH35, or to a named range "P1DSched"?

Thank you very much for your help on this!

 

[Sajukai]

Sorry for the delay responding, yes you can use a range but the user would have to change the whole range at once to qualify.
example range $A$1:$A$10 : changing A1 will result in a case failure as $A$1 does not equal $A$1:$A$10.
example2 range $A$1:$A$10 : selecting A1:A10 entering a value and hitting CTRL+ENTER will result in a match as $A$1:$A$10 does equal $A$1:$A$10.

For the not working, you'd need to post those bits of code in here as you have them to see. Tested and works fine on my end as in the example previously provided.