Toggling Number Highlighting On and Off
The ability to automatically highlight numbers that meet certain criteria can make it a lot easier to find the data you need in a spreadsheet.
The ability to use conditional formatting to format numbers between certain ranges is available as a new feature in Excel 2007. However, the ability to toggle on and off via a checkbox as described in this tutorial is not.
To do this, start by selecting cell E1 (or any other cell you prefer) and name this cell CheckBoxLink using the name box at the far left of the Formula toolbar.
Add a checkbox from the Form Controls (Forms toolbar for pre-2007) to cell F1. Set the cell link of this checkbox to the cell CheckBoxLink by rightclicking the checkbox and selecting Format Control... → Control. Then type CheckBoxLink in the Cell Link box and click OK.
Right-click the checkbox again, select Edit Text, and enter the words Show Me. In column A on another worksheet, enter the numbers 25 to 2500 in increments of 25. Name this range Numbers and hide this sheet by selecting View → Hide under the Windows options (pre-2007, Format → Sheet → Hide).
To enter these numbers quickly, enter the number 25 in cell A1. Then, right-click (Control-click on the Mac) the fill handle (which appears as a small black square at the bottom right of the selection) and, while holding down the right mouse button, drag down to about row 100. Now release the left mouse button, select Series from the pop-up shortcut menu, enter 25 as the step value, and enter 2500 as the stop value. Then click OK.
Select cell B1 of the checkboxes worksheet and name this cell FirstNum. Select cell D1 and name this cell SecondNum. In cell C1, type the word AND. Now, select cell B1 (FirstNum), and press the Ctrl key while selecting cell D1 (SecondNum). Select the Data tab, go to Data Tools → Data Validation (pre-2007, Data → Validation → Settings), select List in the Allow: box, and type =Numbers in the Source: box. Ensure that the In-Cell drop-down item is checked and then click OK. This will give you a drop-down list of numbers 25 through 2500 in both cells.
In cell A1, type the heading Amount. Immediately belowthis, fill the range A2:A20 with any numbers that fall between the range 25 and 2500. Select cells A2:A20 (ensuring that you start from cell A2 and that it is your active cell in the selection), select Home → Styles → Conditional Formatting → New Rule (pre-2007, Format → Conditional Formatting...).
In the dialog box that appears, select "Use a formula to determine which cells to format" (pre-2007, select Formula Is; it now should read Cell Value Is).
Then, in the "Format values when this formula is true" box (Formula box in pre-2007 versions), type the following formula:
=AND($A2>=FirstNum,$A2<=SecondNum,CheckboxLink)
Click Format and set any desired formatting or combination of formatting. Click OK, and then click OK again to dismiss the dialog boxes. Change the font color for cell E1 (CheckBoxLink) to white so that True or False will not show. From cell B1 (FirstNum), select any number and then select another number higher than the first from cell SecondNum (D1).
Check the checkbox, and the conditional formatting you just set will be applied automatically to the numbers that fall between the range you specified earlier. Deselect the checkbox and the formatting will revert to the default.
As you can see, by using a checkbox in combination with conditional formatting, you can do things most people would think is possible only through the use of VBA code.