Whereas the capability of the code in the attached workbook is declared as “Setting Cell Values Depending Upon Another Cell’s Value” its range of application extends from simply adding a date stamp when a cell is set to “Completed” as demonstrated in the example to setting different results in multiple cells in response to various cell contents. In fact, the code is so versatile that any VBA action at all can be prompted by the value of the referenced cell. It is with this versatility in mind that the code was structured into five components as follows:
- Definition of Action and Target cells,
where Action cells are those whose content trigger an action while Target cells are those whose value is set by the program. - Worksheet_Change event procedure, installed at sheet level.
This procedure determines whether the changed cell is an Action cell or not. - Sub ‘CheckAction’,
whose job it is to determine whether the contents of the changed cell now warrants action to be taken. - Sub ‘TakeAction’,
which carries out the pre-determined action, and - Function ‘ActionIndex’
which returns the index number of the Target cell.
Since, due to their brevity and naming, as well as the naming of variables, the code is self-explanatory only the Definitions be discussed here. These consist of two constants declared as Public so that they may be available to all procedures in the project:
Public Const ActionList As String = "E4,E7"
Public Const TargetList As String = "F4,F7"
The requirement is that they hold valid cell addresses, separated from each other by commas, and that the number of elements in both strings be the same. There is no limit to the number of cells that you can list, except that you can’t have less than 1. You can use range definitions like A3:A5 in either of the strings, and the size of the matching strings may be different. For example, you may color the Target range A1:A64000 yellow in response to the Action range C16 being “Stalled”.
All components should be installed in a normal code module as was done in the attached example, except for the event procedure which must be installed in the code sheet of the worksheet that holds the referenced cell or cells.