Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 17 Control Conditional Formatting with Checkboxes

Although conditional formatting is one of
Excel's most powerful features,
it's a nuisance to turn it on and off through the
menus and dialog boxes of the GUI. Adding checkboxes to your
worksheet that turn formatting on and off makes it much easier to
read data in any way you want, whenever you want.

Conditional
formatting, a feature available since Excel 97, applies formats to
selected cells that meet criteria based on values or formulas you
specify. Although conditional formatting is usually applied based on
cell values, applying it based on formulas provides the flexibility
to extend the conditional formatting interface all the way to the
spreadsheet grid.

Setting Up Checkboxes for Conditional Formatting

The checkboxes from the Forms toolbar
return either a TRUE or FALSE
value (checked/not checked) to their linked cell. By combining a
checkbox from the Forms toolbar with conditional formatting using the
Formula Is option (shown in
Figure 2-1), you can turn conditional formatting on
and off via a checkbox.

Figure 2-1. The Conditional Formatting dialog with the Formula Is option

When used in conjunction with a formula (such as the Formula Is option), conditional formatting automatically formats a cell whenever the formula result returns TRUE. For this reason, any formula you use in this hack must return either TRUE or FALSE.

To see what we mean, try this
simple example, which hides data via the use of conditional
formatting and a checkbox. For this
example, we will use the range $A$1:$A$10, filled consecutively with
the numbers 1-10. To obtain a checkbox from the Forms toolbar, select
View Toolbars Forms and click the checkbox,
then click near cell C1 on your sheet to position the
checkbox. Right-click the checkbox
and select Format Control Control. Type C1 in the Cell
Link box, as shown in Figure 2-2, and click
OK.

Figure 2-2. The Format Control dialog

When you select the checkbox floating over cell C1, it will return
TRUE or FALSE to cell
C1. As you do not need to see these
values, select cell C1 and change the font color to White.

Now select cells $A$1:$A$10, starting with
A1. Select Format
Conditional Formatting..., and then select Formula
Is (it will initially read Cell
Value Is). In the Formula box to the right, type
=$C$1. Next,
click the Format tab of the Conditional Formatting dialog, then the
Font tab, and change the font color to White.
Click OK, then OK again.

Select your checkbox so that it is checked, and the font color of the
data in range $A$1:$A$10 automatically will change to white. Unchecking the checkbox will set it back to
normal.

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.
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 (see Figure 2-3).

Figure 2-3. Cell E1 named CheckBoxLink

Add a checkbox from the Forms toolbar to a clean worksheet, call this
sheet Checkboxes, and position it in cell
A1. Set the cell link of this
checkbox to the cell CheckBoxLink by right-clicking 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
Format Sheet Hide.

To enter these numbers quickly, enter the number 25 in cell A1. Then, left-click the fill handle (which appears as a small black square at the bottom right of the selection) and, while holding down the left 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 Data Validation
Settings, and from the Allow: box, select List, and in the Source:
box, type =Numbers.
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 below this, 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), and
select Format Conditional Formatting....

In the dialog box that appears, shown in Figure 2-4, select Formula Is (it now should read Cell
Value Is). Then, in the Formula box,
type the following formula:

=AND($A2>=FirstNum,$A2<=SecondNum,CheckBoxLink)

Figure 2-4. The Conditional Formatting dialog box

Click the Format tab 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 CheckBoxLink (E1) to White so that True or False will not show.
From cell FirstNum (B1), 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 it to its 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.