Applying Conditional Formatting to Multiple Worksheets

Prior to upgrading to Excel 2007, Paula was able to select more than one sheet and apply conditional formatting to a group of cells. But when she selects more than one worksheet in Excel 2007, the conditional formatting option fades. Paula is wondering how she can apply conditional formatting to more than one sheet at a time in a workbook in Excel 2007.

Good question, Paula. It seems that Microsoft did make this change as part of their new user interface. Why? I have no idea, but users are stuck with the results of the change—not being able to apply conditional formatting across multiple worksheets at the same time.

We were able to come up with only two workarounds. The first workaround involves copying the conditional formatting from one worksheet to another. Apply the conditional formatting to the first worksheet, then select all those cells to which you applied the formatting. Next, click the Format Painter (on the Home tab of the ribbon), switch to the target worksheet, and select the cells to which the formatting should be applied. That's it.

The second workaround involves running the macro recorder while setting up the conditional formatting on your first worksheet. You can then re-run the macro as you display each of your other worksheets, in turn. The conditional formats are applied to the same cells on the other worksheets.