XL97: Conditional Format Applies Format for First Condition

Last reviewed: January 15, 1998
Article ID: Q157938
The information in this article applies to:
  • Microsoft Excel 97 for Windows

SYMPTOMS

When you use Conditional Formatting, only the format for the first condition is applied. Second and/or third conditions are ignored.

CAUSE

If you specify multiple conditions and more than one condition is true, Microsoft Excel applies only the formats of the first true condition.

Microsoft Excel is capable of applying different formats to three separate conditions. If the conditions overlap, Microsoft Excel applies the first format to all cells that meet the condition. Microsoft Excel does not apply formats for the second and/or third conditions to data that met the first condition.

WORKAROUND

To apply conditional formats to data that overlaps, do the following:

  1. Define the first condition that will affect the least amount of data.

  2. Define subsequent conditions.

MORE INFORMATION

The following example applies two font formats to cells based on two conditional formats.

Conditional formatting checks the first condition and applies it to all cells that meet the condition. Microsoft Excel checks the second condition and applies it to all cells that meet the condition and cells that were not part of the first condition.

  1. Enter the following in a new worksheet:

    A1: 1 A2: 2 A3: 3

  2. Select cells A1 through A3.

  3. On the Format menu, click Conditional Formatting.

  4. For Condition 1: Click in the first box and click "Cell Value".

  5. Click in the second box and click "Greater than".

  6. Click in the third box and enter the number 2.

  7. Click Format.

  8. On the Font tab, change Color to red.

  9. Click OK and then click Add.

  10. For Condition 2: Click in the first box and click "Cell Value".

  11. Click in the second box and click "Greater than".

  12. Click in the third box and enter the number 1.

  13. Click Format.

  14. On the Font tab, change Color to blue.

  15. Click OK and then click OK again.

Cell A3 has a font color of red because it meets the first condition. Cell A2 has a font color of blue because it meets the second condition. In cell A3 the second condition is ignored because it already met the first condition.

REFERENCES

For more information about conditional formatting, click the Index tab in Microsoft Excel Help, type the following text

   conditional formatting

and then double-click the selected text to go to the "Highlight data that meets conditions you specify" topic.


Additional query words: XL97
Keywords : xlformat kbualink97
Version : WINDOWS:97
Platform : WINDOWS


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: January 15, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.