Link to another workbook using conditional formatting in Excel

Lets call the workbook where you want to enter the conditional format as MyWbk and the other one SrcWbk. This instructions are for excel 2007. Your mileage may vary.

1. Open both Workbooks
2. In MyWbk go to the Formulas Menu, and select Define Name
3. Enter in Name > testname
4. In the "Refers to" field, select click on the range selector (at right) and when the selector opens, select SrcWkb and then the cell you want as source for the format condition.
5. Accept the name creation
6. Go to the cell where you want to enter the conditional format
7. Select the Home Menu / Conditional Formatting / Manage Rules
8. Enter New Rule
9. Select "Use a formula to determine which cells to format"
10. Enter the formula you want, using the defined name. For example =IF(testname=1,TRUE)
11. Select the desired formatting
12. Test the formula changing the value in SrcWbk
13. SAVE SrcWbk

You are done!

Source : http://stackoverflow.com/questions/3146368/link-to-another-workbook-using-conditional-formatting-in-excel