Conditional Formatting based on the date of another column

[Hayley]

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.

[#2]

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

[#3]

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?

[#4]

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

[#5]

Hi Sergei,

Sorry I'm just not with it today - Thanks for all the help!

[#6]

I need sort of the same thing, but I have a 'Date Mailed' column and a 'Date Received' column. I need to automatically color the dates in the 'Date Mailed' column whenever the date there is 45+ days older that the corresponding date in the 'Date Received' column. What is the formula for conditional formatting in this scenario? Thanks!

[#7]

I can't figure out how to make this it's own post as opposed to a reply. Help!

[#8]

@Marcus_Booth

Go to https://techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat and click 'Start a Discussion'.

[#9]

Thank you!

[#10]

@Sergei Baklan how would you add a formula to color column "I" whenever we put a date into column "M"

[#11]

@lithia-Tim313 

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.