Excel/Clear cells contents in one worksheet if cell changes in a different worksheet
Using Excel 2010
I have a worksheet named "Input_Employee" in which a status value (cell C16) may be changed by the user. If this value changes, I wish to clear the contents of the cells in columns C through G in the worksheet "Employee DB" for the corresponding employee record. Using the Worksheet_Change feature in the "Input_Employee" code, I am able to identify the correct row containing the employee data in the "Employee DB" worksheet, but I get a 1004 error on the following line:
Worksheets("Employee_DB").Range(Cells(Row_Counter, EE_BY_Form_Col), Cells(Row_Counter, EE_BY_Mgr_Col)).ClearContents
where Row_Counter is the employee record row in "Employee DB", EE_BY_Form_Col equals 3 (for column C) and EE_BY_Mgr_Col equals 7 9for column G).
since the Employee_DB sheet is not the activesheet then the unqualified Cells refers to the activesheet while the Range refers to the Employee_DB sheet. This is an invalid range reference. The solution is to qualify the cells references. Here is one way
.Range(.Cells(Row_Counter, EE_BY_Form_Col), _
Now everything refers to the Employee_DB sheet and the code should work.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here