XL97: Conditional Format Applies Format for First Condition (157938)


The information in this article applies to:

  • Microsoft Excel 97 for Windows
This article was previously published under Q157938

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.
Modification Type:MajorLast Reviewed:10/22/2000
Keywords:kbualink97 KB157938
©2002 Microsoft Corporation. All rights reserved.