Feb 16 2022 05:28 AM
I would like to have a cell show a color if the date is 30 days less than another date in a different cell. Form instance, if cell I33 says February 16, 2022 and cell G33 says Feb 22, 2022, how to I get cell I33 to show a color?
Thanks
Feb 16 2022 05:46 AM
SolutionSelect I33.
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 'greater than' from the second drop down.
Enter =G33-30 in the box next to it.
Click Format...
Activate the Fill tab.
Select a color.
Click OK, then click OK again.
Feb 16 2022 05:51 AM
Feb 16 2022 09:09 AM
How would I make this work for the whole worksheet?
Feb 16 2022 12:19 PM - edited Feb 16 2022 01:27 PM
Select the entire range that you want to format conditionally. The active cell should be in the first top) row of the selection. In the following, I will assume that this is row 2.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the following formula in the box:
=$I2>$G2-30
Remember, 2 is the row of the active cell - change if the active cell is in another row.
Click Format...
Activate the Fill tab.
Select a color.
Click OK, then click OK again.
Feb 16 2022 01:07 PM
2/22/2022 | 2/15/2022 | 2/17/2022 |
As an example, Feb 22, 2022 is cell I33 (expiry date) and Feb 17, 2022 is cell L33 (task end date. I am trying to have cell L33 filled with a color if the date is too close to the expiry therefore would like it to show a color to tell me this. I need it to show if cell L33 is less than 30 days base on the date in cell I33.
The worksheet has a number of rows with different dates.
Feb 16 2022 01:16 PM
Can you tell us exactly which cells you want to color? Just one column, or multiple columns?
And is row 33 the starting row of the data?
Feb 16 2022 01:22 PM
Feb 16 2022 01:28 PM
OK, so select L3:L70 or even further down if you wish.
L3 should be the active cell in the selection.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the following formula in the box:
=AND($I3<>"",$L3>$I3-30)
Click Format...
Activate the Fill tab.
Select a color.
Click OK, then click OK again.
Feb 16 2022 01:31 PM
That worked! Thank you so much for your patience!
Feb 16 2022 05:46 AM
SolutionSelect I33.
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 'greater than' from the second drop down.
Enter =G33-30 in the box next to it.
Click Format...
Activate the Fill tab.
Select a color.
Click OK, then click OK again.