XL97: Conditional Format Applies Format for First Condition

• 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.

REFERENCES

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

   conditional formatting