XL97: Conditional Formatting Unexpectedly Applied to Text

• Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel 97, if you apply conditional formatting to a range of cells, the conditional formatting may unexpectedly be displayed in cells in the range that contain text.

CAUSE

This problem may occur if you apply a conditional format that uses "greater than," "greater than or equal to," "not equal to," or "not between." For example:

   Cell Value Is greater than 5

   Cell Value Is greater than or equal to 5

   Cell Value Is not equal to 5

   Cell Value Is not between 5 and 12

WORKAROUND

If You Are Using "Greater Than" or "Greater Than or Equal To"

To prevent this problem from occurring, use "between" instead of "greater than" or "greater than or equal to" when you apply a conditional format to a range of cells.

When you use "between," you must enter two values:

• The first value is the same value you were using previously; for example, 5. Note that if you were using "greater than" previously, you should increase the value by a very small amount to account for the difference in behavior between "greater than" and "between"; for example, 5.00000001.
• The second value is a very large number; for example, 9.99E+307.

   Original Condition   New Condition
   -------------------------------------------------------------------

   Cell Value Is        Cell Value Is between 5.00000001 and 9.99E+307
   greater than 5

   Cell Value Is        Cell Value Is between 5 and 9.99E+307
   greater than or
   equal to 5

If You Are Using "Not Equal To" or "Not Between"

To prevent this problem from occurring, do not apply conditional formatting that uses either "not equal to" or "not between" to a cell that contains text.

STATUS

This behavior is by design of Microsoft Excel 97.

MORE INFORMATION

In Microsoft Excel 97, you can use conditional formatting to force cells within a range to use specific formatting if certain conditions are true. For example, you can use conditional formatting to force all cells whose values are greater than 100 to appear in a bold, red font. To apply conditional formatting to a range of cells, select the range of cells, and click Conditional Formatting on the Format menu.