Forum Discussion
Highlight cell based on whether a deadline was met
Hi
I have many spreadsheets to track the completion of tasks. Each task is allocated a row on the spreadsheet and given a deadline in one column and then the completion date is recorded in another column.
I would like to highlight the completion date cell in red if the deadline was not met or in green if the deadline was met.
I would then like to apply this to all rows within the spreadsheet. This needs to be row specific because rows do not relate to one another.
I have tried conditional formatting the completion date cell with greater than and less than its related deadline cell. However without manually doing this for every row i do not know how to apply it to the entire spreadsheet. I tried format painter but it just formats based on the one completion date cell that i highlighted rather than adapting to the row.
Is anyone able to help, or link me to a tutorial on this?
I can only find tutorials for highlighting based on one cell for the entire spreadsheet, not based on a specific column within the row.
I selected the range to format: J7:J39,
J7 was the active cell within the selected range.
On the Home tab of the ribbon, I selected Conditional Formatting > New Rule...
I selected 'Format only cells that contain'.
I left the first drop down set to 'Cell Value'.
I selected 'greater than' from the second drop down.
In the box next to it, I entered the formula
=H7
Since there is no $ before the row number 7, Excel will automatically adjust it to =H8 for J8, to =H9 for J9, etc.
I clicked the Format... button.
I activated the Fill tab.
I selected red.
I clicked OK twice.
The overdue dates were now red.
Next, I repeated the series of steps described above, but I selected 'less than or equal to' from the second drop down, and I selected green as fill color.
The timely dates were now green, but the empty cells in J7:J39 too.
So I went through the same steps again, but this time with 'equal to' from the second drop down.
I entered ="" in the box next to that drop down. and selected 'No color' in the Fill tab of the Format cells dialog.
This removed the green color from the empty cells.
7 Replies
Let's say the deadline dates are in D2:D100 and the completion dates will be entered in E2:E100.
Select E2:E100.
The top cell of that range, i.e. E2, should be the active cell in the selection.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'less than or equal to' from the second drop down.
In the box next to it, enter the formula =D2
Click Format...
Activate the Fill tab.
Select green.
Click OK, then click OK again.
Repeat these steps, but with 'greater than' instead of 'less than or equal to', and with red as fill color.
Finally, repeat them again, this time with 'equal to', with ="" as formula, and with 'No color' as fill color.
- Thank you for your advice. Unfortunately this hasn't solved my issue but i appreciate your efforts.
This is still highlighting the cells based on the value in D2 (based on your example).
I need:
E2 to highlight based on D2
E3 to highlight based on D3
E4 to highlight based on E4
Your suggestions has E2, E3 and E4 all highlighting based on contents in D2. 😞
Unless i am doing something wrong with the 'active cell'. I assume this means the cell that shows as 'active' as in the clear cell rather than shaded ones. In other words, the first cell you click to then drag for your entire selection.I assume this means the cell that shows as 'active' as in the clear cell rather than shaded ones. In other words, the first cell you click to then drag for your entire selection.
Yes, that is what I meant.
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
