The problem with making the shading conditional is that once it's been removed it's not obvious to the user what fields are editable, or require input, and which are not.
It's not a right or wrong -- it just depends on usage. At some point, in this type of sheet, you'd need to clear the input cells -- and then they'd be shaded again, automatically. It's a good question!
so conditionally format the cell as a second color to indicate input received in an alterable cell
This would work too, especially if it's a consistent format throughout the org.
Using Excel 2010 for this example, if you highlight the cell with a color that means "input needed" (lets say red), then the conditional formatting rule could be "ifnumber()", "iftext()" or a formula that confirms that the input is in an expected form with a different color (say green). Now the cell appears red by default (needing input) and green when filled in, continuing to show a highlight indicating where user input is allowed. Since multiple rules are permitted to apply to a single cell, you could even change the highlighting to distinguish between valid input and invalid input or any other condition you can put in a formula like highlighting if the input is outside an expected range.
The simple solution would be to add a second conditional formatting rule with a different cell fill such as the following "=NOT(ISBLANK(B1))"
I find that simply adopting a different colour typeface and bold type is sufficient for identifying input cells. Quick and simple for personal use, but of course it does not provide protection in a multi-user environment.
In 2007 onwards you will not require to write formula. I use built-in feature to indicate the cells that need to be filled -in using condition "Format only cells that contain" and select Blank from drop down menu at Format only cells with
- Keyboard Shortcuts: