Hack 22 Turn Conditional Formatting and Data Validation On and Off with a Checkbox

   

Hack 22 Turn Conditional Formatting and Data Validation On and Off with a Checkbox

Data validation can make it far less likely that a user accidentally will enter incorrect data. Sometimes, however, you might need to make it easier to enter data that otherwise would be flagged as incorrect by conditional formatting or blocked completely by the validator .

Usually, you would enable users to enter data that otherwise would be flagged as incorrect by removing conditional formatting and/or data validation from the cells . There is an easier way, however: you can combine a simple checkbox from the Forms toolbar with data validation.

For this example, you'll apply conditional formatting to a range of cells so that any data appearing more than once is highlighted for easy identification. We'll assume your table of data extends from cell $A$1:$H$100. To conditionally format this range of data so that you can identify duplicates requires a few steps.

Select cell K1 and name this cell CheckBoxLink by typing the name into the Name box to the left of the Formula bar and pressing Enter. If the Forms toolbar is not already showing, right-click any toolbar and select Forms, then click the Checkbox icon. Now click your worksheet somewhere outside the range A1:H100 to add the checkbox to the worksheet.

Right-click the checkbox and select Format Control Control. In the Cell Link box, type the name CheckBoxLink and click OK. Select cell A1, then drag and select a range down to cell H100. It is important that cell A1 is the active cell in your selection. Select Format Conditional Formatting..., and from the box with the Value Is cell, select Formula Is. In the box to the right of Formula Is, enter this formula (as shown in Figure 2-7):

 =AND(COUNTIF($A:$H0,A1)>1,CheckboxLink) 

Click the Format tab and then the Patterns page tab and select a color you want to be applied to duplicated data. Click OK, then OK again.

Figure 2-7. Conditional Formatting dialog showing formula to conditionally format a range to highlight duplicates

Although the checkbox you added to the worksheet is checked, the cell link in K1 (CheckBoxLink) will read TRUE and all duplicates within the range $A$1:$A$100 will be highlighted. As soon as you deselect the checkbox, its cell link (CheckBoxLink) will return FALSE , and duplicates will not be highlighted.

The checkbox gives you a switch so that you can turn conditional formatting on and off from the spreadsheet, with no need to return to the Conditional Formatting dialog box. You can apply the same principle to data validation when using the formula option.

This works because you used the AND function. AND means two things must occur: COUNTIF($A$1:$H$100,A1)>1 must return TRUE , and the cell link for the checkbox (CheckboxLink) also must be TRUE . In other words, both conditions must be TRUE for the AND function to return TRUE .