Despite The Warning, Conditional Formatting Can Do Its Job On Two Different Worksheets
Q. I loved the article “Spreadsheet Smarts” by Justin D. Stein ( JofA, Jan.00, page 53). I’m particularly interested in the Conditional Formatting function and its potential benefit in financial statement preparation. But I need to apply the function to compare values on two different worksheets. However, when I try it, I get error messages. What’s up?
A. The author replies: It’s true that, if you want to use a formula within the Conditional Formatting function to compare the values of two cells from different worksheets, you will receive the error message: “You may not use references to other worksheets or workbooks for Conditional Formatting criteria.” However, you can get around this obstacle by creating a named range for at least one of the cells you wish to compare.
For example, if you want to compare the values of two cells from separate worksheets, Sheet1!A1 and Sheet2!B1, you need to create a range name for any cell not residing on the current worksheet (Sheet1). For example, you can name Sheet2!B1 “Total,” and you can now use Total in a formula to represent Sheet2!B1.
| Exhibit 2 |
 |
| You can adjust Conditional Formatting so it can compare two values in more than one workbook. |
The simplest way to create a range name is to select a cell or a range and type the name of the range in the range drop-down window to the left of the formula bar (see exhibit 2, above). The formula bar sits just beneath the toolbars at the top of your screen and shows you the contents of the selected cell. If I select Sheet2!B1, the range drop-down window contains B1. Click in the window and type Total, then press Enter. The name of your range, Total, now appears in the window.
Now we can rewrite our original formula for the Conditional Formatting for cell Sheet1!A1 to refer to Total, rather than Sheet2!B1.
After I set the format I want, the function will accept my formula and work properly. Now you can compare two values anywhere in the workbook.
