finding data entry required cells by highlighting.

  • Thread starter Thread starter gtslabs
  • Start date Start date
G

gtslabs

I want to highlight the cells that are only to be used for data entry.
But I dont want the highlight to print.
It would also be nice to have these cells change color once the data
was changed so the person entering knows if they missed any cells. Is
something like this possible?
 
Hi,

Are the cells blank before the entries are made or are you altering existing
values?

If the cells are blank before the user enters data:-

Use conditional format and set the background color condition to "cell is
equal to" and enter 0 (Zero) for the value to compare. When the user enters
data, the background color will return to normal and will not print and the
user would be able to see the colored cells which do not have entries.

Regards,

OssieMac
 
OK I have thought about some options using macros.

If anyone else reads this then feel free to offer suggestions.

Option 1:-
Macro1: Worksheet activation event which runs automatically to highlight the
required cells with an interior color each time the worksheet is activated.
Macro2: Worksheet change event that will change the interior color in the
activecell when the user changes the value.
Macro3: Workbook_BeforePrint macro to turn off all color before printing.

The disadvantage of Option 1 is that if the user changes worksheets and
returns to the worksheet to be edited then the cells will all be highlighted
again.
However, you can save and re-open the workbook just so long as you do not
change worksheets.

Option 2:-
Macro1: Button operated macro to run as per Macro1 above but only runs when
the user clicks the button.
Macro2: Same as Macro2 above.
Macro3: Same as Macro3 above.
The advantage of this is that the user controls when the cells will be
highlighted instead of the macro running automatically every time the user
changes worksheets.

If you would like me to develop these macros for you then please answer the
following questions:-

do you need instructions to copy the macros into your workbook etc
what version of Excel do you have
the worksheet name
list of cells to be highlighted
background color before editing
background color after editing
I suggest that you try out some background colors for
both before and after editing because with some it is
hard to read the cell contents.

Regards,

OssieMac
 
What about having just unlocked cells a certain color, then turning
those cells format on and off when printing?
 
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 don’t 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. (Don’t forget to
check the box to allow formatting.)

Hope it all works well for you.

Regards,

OssieMac
 
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
 
Back
Top