Conditional Formatting From Different Sheet

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 on Sheet2.

Conditional Formatting Workaround for Earlier Versions

In earlier versions of Excel, you can't refer to another sheet in conditional formatting or data validation. With data validation, you can't even click on the other sheet, to try and create a reference. In conditional formatting, you can enter the reference in the dialog box, but then Excel yells at you.

However, you can refer to named ranges anywhere in the workbook, in both conditional formatting and data validation, so you can use that as a workaround.

To highlight the cells in Excel 2007 and earlier versions:

• On Sheet2, name cell A1 as DataStart02

• 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
  
• Type an OFFSET formula that refers to the named range on Sheet2. This will refer to the cell on Sheet2 that is in the same row and column as the active cell.

=OFFSET(DataStart02,ROW()-1,COLUMN()-1,1,1)

• Click OK

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

Excel Tweet of the Day

I can remember 1970, and hadn't even touched a computer yet. I was in classes though!

If you're writing Excel VBA code NEVER USE CLASSES. Seriously buggy. Just make believe it's 1970 and OOP hasn't been invented yet.

For more entertaining and enlightening Excel tweets, culled from the thousands posted every day, see the Excel Theatre Blog.

______________