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.
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. 🙂
For some reason it's hard to get it right directly in Sharepoint.
I downloaded the workbook, edited it and attached it here.
- Wow yes that seems to have worked thank you!
Could you talk me through how to do it? I need to do this across about 5 different spreadsheets! 🙂
Thank so much!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.