Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Conditional Formatting

Conditional formatting is one of the most powerful features available for Excel user interface construction. It enables you to substitute simple formulas for what would otherwise be reams of VBA code. Conditional formatting works by modifying the appearance of the cells it has been applied to only if one or more conditions that you specify have been met. Conditional formatting overrides any style setting when the condition is triggered. When the condition that triggered the conditional formatting is no longer true, the affected cell regains its original format.

The two most common uses for conditional formatting in Excel user interface development are the creation of dynamic tables and calling out error conditions.

Creating Dynamic Tables

When building nontrivial worksheet-based user interfaces, you will often be faced with the problem of providing a table that in extreme cases will allow the entry of some large number of rows but for the most common scenarios will only require a few. Rather than hard-coding a visible table with the maximum possible number of rows, you can use conditional formatting to create a table that expands dynamically as data is entered into it. We demonstrate how this is done beginning with the sample table shown in Figure 4-24.

Figure 4-24. Data-Entry Table Prior to the Addition of Dynamic Formatting

Let's assume this table really requires 200 rows for the largest projects but that most users only need a few rows of input. Therefore, you want to hide the unused area of the table. As you can see, the first step in creating a dynamic table is to draw the entire table on the worksheet. You then use conditional formatting to hide the unused area of the table and reveal rows dynamically as needed. The trigger for displaying a data-entry row will be the user entering a new name into the Item Name column. For that reason, we always need to leave an empty Item Name entry cell at the bottom of the table.

When creating a dynamic table, it's a good idea to also create an outline showing the extent of the table in one of your hidden columns. After we've added the conditional formatting, the table will disappear. This makes the table difficult to maintain if you haven't provided yourself with a visual marker indicating its extent. The empty bordered area in column A serves this purpose in our example. This area doesn't need to be empty. It could include error-checking formulas, for example. As long as it gives you a visual indication of the extent of the hidden area of the table, it serves its purpose.

Our dynamic table requires three different conditionally formatted sections. Referencing Figure 4-25, the first section will encompass range C3:C12, the second section will encompass range D3:F12 and the third range will encompass range G3:G12. We'll add the conditional formats one step at a time so you can see the results as they occur. To make the operation of the conditional formats more obvious we'll add data to the first row of the table. Keep in mind that the purpose of all three conditional formatting sections is the same: to simulate the appearance of a table that is just large enough to hold the data that has been entered into it. Figure 4-25 shows the table with the first section of conditional formatting completed.

Figure 4-25. Conditional Formatting for the First Column

[View full size image]

In addition to the purpose described above, the first conditional format serves to leave a blank cell in front of the first unused table row in order to help prompt the user to enter the next item. The second conditional format is shown in Figure 4-26. It clears all unused rows in columns D through F and draws a bottom border below the first unused row in the table, thereby helping to complete the table outline.

Figure 4-26. Conditional Formatting for the Remaining Columns Within the Table

[View full size image]

You can see the white border on the far right side of the table is missing in Figure 4-27. The purpose of the third conditional format is to complete the simulated table by drawing this border. Figure 4-27 shows the third conditional format.

Figure 4-27. Conditional Formatting Outside the Table to Create the Right-Hand Border

[View full size image]

Figure 4-28 shows the fully formatted table with some additional entries. Each time a new entry is made, the conditional format reveals the row in which the entry was placed and adds a new prompt row below it.

Figure 4-28. The Complete Dynamically Formatted Table

The one major caveat when considering the use of conditional formatting to create dynamic tables is that calculation must be set to automatic in order for it to work. If your user interface is so calculation intensive that you need to set calculation to manual, then you cannot create dynamic tables using this method (or use any other type of formula-based conditional formatting for that matter).

Calling Out Error Conditions

Conditional formatting can also work alone or in concert with formulas in hidden rows and columns to highlight invalid entries as soon as they are made. This should not be your method of first choice for pointing out data-entry errors. Always try to use data validation to prevent data-entry errors from being made in the first place.Figure 4-22.

Figure 4-29. The Error Check Formula Column for the Conditional Format

Even though both columns' lists are data validated, an error can creep in if the user initially selects a valid category and item combination but then accidentally changes the category name at some later point in time. This type of mistake cannot be prevented by data validation, so we need to provide some visual indication that there is a mismatch between the category and item selections if this error occurs. This is a task for conditional formatting.

As you can see in Figure 4-29, we've inserted a second hidden column. In this column we've created an error check for each row that verifies the entry selected in the Item column is valid for the selection in the Category column.

The error check formula is a bit complicated, so we break it down in Listing 4-1. Keep in mind that the purpose of the error check formula is to return True if the corresponding row in the table has a data-entry error and False otherwise.

Listing 4-1. The Error Check Formula Outlined

=IF(ISBLANK(E3),FALSE,
IF(D3=$A$3,
ISERROR(MATCH(E3,$A$7:$A$10,0)),
ISERROR(MATCH(E3,$A$13:$A$16,0))
)
)

The only type of error that can occur in this situation is the Item column entry not matching the Category column entry. If there is no Item column entry, the row is not complete and we cannot determine the validity of the Category column entry. The outer IF function checks for this condition and returns FALSE if this is the case. When there is an entry in the Item column, the inner IF function determines the correct list from which to try and locate the Category entry. The formula then uses the MATCH function wrapped in the ISERROR function to return TRUE if the Category entry is located in the correct list or FALSE if it isn't.

The next thing we do is add a conditional format to the table that checks the value of the HasError column. If the HasError column indicates there is an error in one of the table rows, our conditional format will give that row a bright red shade. Error condition highlighting is one exception to the rule of not using garish colors in your user interface. We do recommend using red, however, because this is almost universally recognized as a warning color. Figure 4-30 shows the conditional format required to accomplish this.

Figure 4-30. Setting Up Conditional Formatting to Flag an Error Condition

[View full size image]

The result of the conditional format in response to an error condition is shown in Figure 4-31, where we've changed the Category column entry in the second table row from Vegetables to Fruits so it no longer matches the entry in the Item column.

Figure 4-31. Conditional Formatting Flagging a Bad Entry in the Table