XL97: Conditional Formatting Unexpectedly Applied to Text (173216)
The information in this article applies to:
- Microsoft Excel 97 for Windows
This article was previously published under Q173216 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.
WORKAROUNDIf 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.
Modification Type: | Major | Last Reviewed: | 10/22/2000 |
---|
Keywords: | kbprb KB173216 |
---|
|