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.
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. 🙂
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!