Hi yet again,

I have modified the before print macro to suppress the macro from running if
you print from another worksheet in the workbook. Could give real problems if
you have a need to do that. Use the following macro in lieu of my previous
one.

Note that you have to edit the sheet name to match your sheet name.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'Edit the following line and change Sheet1 to
'the name of your worksheet
If ActiveSheet.Name = "Sheet1" Then
'Cancel original print call
Cancel = True

'Turn off interior colors for all cells
ActiveSheet.Cells.Interior.ColorIndex = xlColorIndexNone

'Disable events to prevent recursive calls to this procedure
Application.EnableEvents = False

'Print the worksheet
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

'Reset the interior ColorIndex for required cells
'Edit and add rows of code to cover all required cells
'ColorIndex 6 is Yellow
ActiveSheet.Range("A2").Interior.ColorIndex = 6
ActiveSheet.Range("A4").Interior.ColorIndex = 6
ActiveSheet.Range("B5").Interior.ColorIndex = 6
ActiveSheet.Range("C7").Interior.ColorIndex = 6
ActiveSheet.Range("D8").Interior.ColorIndex = 6

'Re-enable events
Application.EnableEvents = True
End If

End Sub

Regards,

OssieMac



"OssieMac" wrote:

Hi again,

I believe that I have created macros to achieve the following:-

1. Changes the color after the cell is edited.
2. Turns off the color before printing.
3. After printing, re-sets to original pre-edit color.

I don't know if you require instructions to copy the macros and get them
running so the following is a guide in case you require it:-

Ensure that you make a backup copy of your workbook in case the macros do
not work as you anticipate.

Open the workbook and select the required worksheet.
You will need to have your options set to allow macros to run.
Unprotect the worksheet. (Later when you re-protect it you will need to
check the box 'Format Cells'.)
Alt/F11 to open the VBA editor.
Double click ThisWorkbook in Project Explorer (In left column.)
Copy the following macro (from Private Sub to End Sub) and paste it into the
white area on the right.

Note that after pasting the macro into the VBA editor, green text that is
preceded with a single quote are comments and do not form part of the code.
They are for information only but can be left in situ.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'Cancel original print call
Cancel = True

'Turn off interior colors for all cells
ActiveSheet.Cells.Interior.ColorIndex = xlColorIndexNone

'Disable events to prevent recursive calls to this procedure
Application.EnableEvents = False

'Print the worksheet
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

'Reset the interior ColorIndex for required cells
'Edit and add rows of code to cover all required cells
'ColorIndex 6 is Yellow
ActiveSheet.Range("A2").Interior.ColorIndex = 6
ActiveSheet.Range("A4").Interior.ColorIndex = 6
ActiveSheet.Range("B5").Interior.ColorIndex = 6
ActiveSheet.Range("C7").Interior.ColorIndex = 6
ActiveSheet.Range("D8").Interior.ColorIndex = 6

'Re-enable events
Application.EnableEvents = True

End Sub



In the project explorer again double click the sheet name matching your data
worksheet.

Copy the following macro into the white area.

Private Sub Worksheet_Change(ByVal Target As Range)
'Change the interior color of the edited cell to green
Target.Interior.ColorIndex = 4
End Sub



Double click ThisWorkbook again and edit the section near the end of the
macro to set the ranges of the cells to be edited that will be colored
yellow. You can use Alt/F11 to toggle between the Worksheet and the VBA
editor.

When finished editing, close the VBA editor by clicking on the X top right
with the red background.

I suggest that you save and test prior to re-setting protection.

Save the workbook. (If xl2007 then save as Macro Enabled workbook.)

The macro to change the color of cells as they are edited runs automatically
each time a change is made on the worksheet. The other macro to return the
colors of the cells to their pre-edit color runs whenever you select print.

If you need to get back to the VBA editor, then Alt/F11 toggles between
worksheet and VBA editor.

If you dont like the colors I have used then you can look up colorindex
values. Select Help while in the VBA editor. (this is a different help to
worksheet help so make sure you are in the VBA Editor when you select Help).

In xl2007 enter colorindex as one word in the search and then select
colorindex property.

In earlier versions enter patterncolorindex as one word in the Answer Wizard
search and then select colorindex property.

When you have finished testing, re-protect the worksheet. (Dont forget to
check the box to allow formatting.)

Hope it all works well for you.

Regards,

OssieMac