Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding data entry required cells by highlighting.
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding data entry required cells by highlighting.
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding data entry required cells by highlighting.
Thanks for the good idea however I already have values in them that
need changed. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding data entry required cells by highlighting.
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding data entry required cells by highlighting.
What about having just unlocked cells a certain color, then turning
those cells format on and off when printing? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding data entry required cells by highlighting.
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding data entry required cells by highlighting.
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Entry That Locks Selected Cells From Any Data Entry. | Excel Worksheet Functions | |||
is it possible to make a cell in excel as entry required | Excel Discussion (Misc queries) | |||
Finding and Highlighting Similar but not Identical Cells in a Colu | Excel Discussion (Misc queries) | |||
Highlighting blanks via GO TO SPECIAL is not highlighting blank cells - HELP, I'm totally stuck. | Excel Discussion (Misc queries) | |||
highlighting required fields | Excel Worksheet Functions |