Google-sheets – How to change both the contents of a cell and its conditional formatting based on a cell in another column

Assuming the actual finishing date is in Column F and the sheet is called Sheet1, paste the code below inside your spreadsheet under Tools >> Script editor, then hit Save.

/***
* Color Code+ 
* Conditional formatting with cell referencing for Google Spreadsheets
* Support: scripts@bryanp.com
**/

function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('Sheet1');
var values1Rule1 = s.getRange('E1:E').getValues();
var values2Rule1 = s.getRange('F1:F').getValues();
var range3Rule1 = s.getRange('E1:E');
var color1 = 'red';

for (var row in values1Rule1) {
for (var col in values1Rule1[row]) {
if (values1Rule1[row][col] == values2Rule1[row][col]) s.getRange(s.getRange('E1').offset(row, col, 1, 1).getA1Notation()).setBackgroundColor(color1);
else s.getRange(s.getRange('E1').offset(row, col, 1, 1).getA1Notation()).setBackgroundColor('white'); }}
};

I used this script generator to make creating the code easier. You can adapt it to your needs.

Here is what you can do:

1. RightClick B2 → select Conditional formatting → select Custom Formula is (from the List)
2. Enter the following formula in the textbox: =b2>=c2
3. Check the Background Color and select green color
4. Click on Add another rule
5. Select Custom Formula is (from the List)
6. Enter the following formula in the textbox: =b2<c2
7. Check the Background Color and select red color
8. Click on Save Rules
9. Highlight B2 and click on Paint Format option on the tool bar.
10. Now click on cell B3 and drag until cell - B117 (this should apply the conditional formatting on cell ranging from B3-B117)

And for your second condition (i.e., to highlight D with green if it is greater than or equal to E, red if it is less than E) - simply repeat the above steps by interchanging D2 for B2 and E2 for C2.