A nice new feature in Excel 2010 is the ability to refer to a different worksheet when creating conditional formatting and data validation. Let's take a look at how the improved conditional formatting feature works, and create a workaround for older Excel versions.

Compare Cells on Two Sheets

In this example, you'd like to highlight the cells on Sheet1, if they're higher than the matching cell on Sheet2.

To highlight the cells in Excel 2010: Select cells A1:C8 on Sheet1, with A1 as the active cell On the Ribbon, click the Home tab, and click Conditional Formatting Click Highlight Cell Rules, then click Greater Than In the Greater Than dialog box, click in the cell reference box Click on the tab for Sheet2 Select cell A1 on Sheet2, and click OK

The cells on Sheet1 are now highlighted if their value is higher than the matching cell

Continue reading Conditional Formatting From Different Sheet