Forum Discussion
Conditional Formatting based on the date of another column
Hi,
I'm trying to conditionally format cells in column A based on their related to cells of the same row in column J.
Essentially if the date in column A is before the day in column J I want the cell to go green
If the date in column A is after the date in column J I want the cell to go red
Is this possible? I cannot find a way to do it.
10 Replies
Hi Hayley,
You need to apply two rules, one for each color. For example, for the green
New rule-> Use the formula...
and
=$A1<$J1
applying to your range
Select I2:I50 or however far down you want. I2 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=M2<>""
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.
Thanks, how do I do that for the whole column.
The values in both columns change with each cell, will I have to conditionally format each cell individually with the two rules?
Hi,
Okay, step by step.
Select you data range in column A and generate new rule
It'll be applied to the selected range, you may correct it if necessary
All cells in that range for which the condition returns TRUE will be highlighted
