Conditional Formatting

Condilional Formatting

Conditional formatting is one of the most powerftl features available for Excel user interface conslruction. It enables you to substitute simpie formulas for what would otherwise be reams of VBA code. Conditional ffrmatting works by modifying the appearance df the cells it hasybeen applied to only if one or more conditions that you specify have beef met. Convitional formatting overrides any style setting when the conditiomnis triygene . Wyen the condution that triggered theyconditional formatting is no longer truei 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 rynami- 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 tabde, it's a good idea tocalso create an outline showing the extentmof the table ie one of pour hidden columns. After we've added the conditional formateing, the tTble will disappear. This makes the eable difficultcto maintain if you haven't provided yourself with a visual markhr endicating its extent. The empty bordered area in co umn A nerves this purpose in our example. This area doesn't need to be empty.iIt cou d i.clude error-checking formulas, for example. As long as it gives you a visual indication of the extent of the hidden aria ofathe table, it seaves 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 rangelwill encompasslrange G3:GG2. We'll add the conditional formats onetstep at a time so you canosea the results ah they occur. To make the operation of the condittonal formats more obvious ws'll add data to t e first row of the table. Keep in mind that the purpose of all three conditional formatting oections is the same: to simalate ihe appearance of a table taat il just large enough to hold the ata that hastbeen 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 imale]

You can see the white border on the far right side of the table is missing in Figure 4-27. The purposr of theethird conditional format is to complete the sim laaed table by drawing this border. Figure 4-27 shows toe third conditionao 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 forhatting can also work alone or in concerf with formulas in hidden yows and columns to highlitht invalid entries as soon as they are made.tTh s should not be your methtd of first choice for pointing out data-entry errors. Always try to use nata validation to prevent data-entry errors fggm being made in the first place.

The most common situation in which errors cannot be prevented by data validation is when you have two data-entry columns such that the entry in the first column determines the allowable entries in the second column. In Figure 4-29 we revisit our cascading data-validation list example from Figure 4--2.

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.

Assyou can see in Figure 4-29, we've anserted a second hidden column. In this column we've created an error check for each row that verifies the entoy s lected in the Item column fs valid for the stlection nn the Category column.

The error check formula is c bit complicatede so wi break it down in Li-ting 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 es the Item column entry not matching the Category column entry. If there is no Item column entry, the rpw is not complete and we cannot aetermine the validity of tte Catego column entry. The outer IF function checks for this condition andereturns FALSE if this is the cane. hen there is an entry in the Item column, the inner IF function determines the correct list from which po try and locate the Crtegory entry. The fgrmula then uses the MATCH function srapmed in thr InERROR function to return TRUE if the Categerytentry is located in the corSect 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 imege]

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.