Our objective is to protect all the cells except the yellow cells. These will be our data input cells.
Normally when we enter data into a cell and press the TAB key, the cursor moves one cell to the right. When we apply sheet protection to all the cells except the yellow cells, pressing the TAB key will cause the cursor to move to the next unprotected cell. In this case, the next yellow cell.
This is a simple matter of unchecking the Protect feature for each yellow cell.
Some users would select each and remove the feature one at a time. This not only tedious but also time-consuming in cases where there are dozens or hundreds of cells to adjust.
It doesn’t take long to realize that you can select the first cell, hold down the CTRL key and then select the remaining cells. This is faster, but still tedious when working with many cells.
We want a FAST way to select ALL cells that are of a specific color.
The first step is to highlight the range of cells that contain cells to be omitted from sheet protection.
With the range selected, press CTRL-F to activate the Find feature. On the Find tab of the Find and Replace dialog box, press the small down arrow button to the right of the Format button and select “Choose Format From Cell…”
You will be presented with a standard cell selection cursor, but one with an additional image of an eyedropper to the right of the thick plus symbol.
Place this cursor over any of the yellow cells and click. The preview feature of the Find tool will display the cosmetic attributes of the selected cell.
Be aware that when selecting a reference cell with this technique we are not only capturing the color of the cell but ALL cosmetic setting s of the cell; font color, font style, font size, number formatting, bold, italic, underline, etc.
Click the Find All button at the bottom of the Find dialog box. This will display a list of cells that possess the same cosmetic features as the reference cell.
To select all the discovered cells, press CTRL-A in the Find and Replace dialog box. This will select all the discovered items displayed in the lower panel of the dialog box.
With the cells selected, press CTRL-1 to return to the Format Cells dialog box. Return to the Protection sheet and remove the check from the Locked feature.
Click anywhere on the sheet to remove the sell selection.
Apply sheet protection in the same manner as before: Review (tab) -> Protect (group) -> Protect Sheet.
To test the new behavior, select the first yellow cell and press the TAB key. The cursor will jump immediately to the next yellow (unprotected) cell.
If you press the TAB key too many times and skip a cell, you can hold the Shift key and press TAB to perform reverse navigation.