As of now (July 2013), Google Spreadsheet doesn't support Conditional formatting by comparing two cells and setting format on any of the two cells or on any third cell. To overcome this I have developed a script.
To install this script go to Script Gallery from your Google Spreadsheet and then search for "Kishan's Conditional Formatting" and then install it.
Here is the screenshot of Sheet 'Conditions' of my spreadsheet, from which the script will copy this sheet to your spreadsheet:
And here is the link of this Spreadsheet.
Now, you have to set all the conditions on this Sheet. And make sure you never rename this sheet, as the script will take inputs of conditions from this sheet and perform its execution.
In Sheet 'Conditions' you have to input two cells for comparison, I will refer them as Cell1 and Cell2 for the rest of this post. And after comparing these cells with condition, you can change the background color of any one cell of the spreadsheet, and I will refer this cell as Cell3.
How to input the Cell1 and Cell2?
You have to put the Sheet name of Cell1 in Column A and near to that cell you have to put Cell2, that is in Column B. Then you can input the condition in Column C. And similarly, you need to input Cell2 in Column D and E.
And in Column F and G, you need to input Cell3, (Cell3 can be the Cell1 or Cell2 or any other Cell from the spreadsheet). Then Column H contains the color that you need to format the background of Cell3 if condition is true. And put the color in Column I, for if condition is false.
NOTE: You need to input values only up to Column I and leave the rest. Don't try to edit the Columns J,K,L,M...
When the script executes, it will put the status for each condition in Column J. So if there is any error (like if you don't input correct values in Cell1 or Cell2 or Cell3) then you will get Error message in Column J. And if no error then it will show "Done!"
Column K is for getting the list of all sheet in your current Spreadsheet, so that we can have the drop down list (data validation) in Column A,D and F.
Note: This script will auto execute each and every time you make any edit on your spreadsheet, as I have called function ConditionalFormatting() in onEdit(e) event. If you notice that it has reduced the speed of your spreadsheet then you can comment this one line by having two forward slashes, that is "//", in front of the ConditionalFormatting().
By doing so script will stop execution for each and every edit and then you can manually call this function from the "Conditional Script" Menu on the "Tool Bar".
If you have any further questions, then let me know by posting it in the comments below...