Forum Discussion
Highlight cell based on whether a deadline was met
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.
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?
Ok - https://diagenics-my.sharepoint.com/:x:/g/personal/emma_diagenics_co_uk/EUs4gUn6vm5DkpsOtoZ3VXQBqgkpdVU-FRMnnstazbhdGA?e=VeLX3q
In this case I need the 'Date Closed' J column to highlight in relation to the 'Target Closure Date' column H.
Thank you. 🙂
