Article ID: 173216
Article Last Modified on 10/22/2000
APPLIES TO
- Microsoft Excel 97 Standard Edition
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.
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: kbprb KB173216
