XL97: Conditional Formatting Unexpectedly Applied to Text

ID: Q173216

The information in this article applies to:

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

Specifically, the problem occurs because any text string has a value greater than any number in Microsoft Excel. Microsoft Excel 97 does not account for this fact when you use conditional formatting.

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.

For example:

   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

When you do this, the conditional formatting is not displayed in cells that contain text.

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.

Additional query words: XL97

Keywords          : xlformat 
Version           : WINDOWS:97
Platform          : WINDOWS
Issue type        : kbprb

Last Reviewed: November 2, 1998