Multiple conditional formatting

[iluvsafc]

Hi all, this one has been getting on my nerves for a while now! Hopefully someone can help. I have some basic formula to work out the date 6 months after and nine months after column I.

I also have some conditional formatting to turn columns J & L red if the date exceeds TODAY() which also works fine.

What i am trying to do is either disable the conditional formatting in column J if the review has been completed and the same in L or create a new conditional formatting rule that turns the columns back to zero formatting if there is a review or an exit meeting entered.

Its the multiple formatting of a single cell based on the value of another cell that i'm struggling with or am i over complicating this?

Any advice would be much appreciated!

[TABLE="width: 500"]
<tbody>[TR]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]Date started[/TD]
[TD]Review Date (6 months)[/TD]
[TD]Review completed date[/TD]
[TD]Exit meeting (9 Months)[/TD]
[TD]Exit Meeting Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/01/2019[/TD]
[TD]=EDATE(I1,6)[/TD]
[TD]01/10/2019[/TD]
[TD]=EDATE(I1,9)[/TD]
[TD]07/08/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

You just need the one CF formula. It would be something like this for column J
=AND(J2>TODAY(),K2="")

and similar for column L

 

[iluvsafc]

Thanks for the reply. If i apply that formula to the first cell in column J is there an easy way of copying that conditional formatting formula to the other cells in J column? Its just that J2 is coded into the format and doesn't change when using autofill. Thanks

 

[Peter_SSs]

If you select J2:Jxx before applying that CF then Excel will automatically adjust for all the other cells you have selected.

If you have only applied it to J2 then select J2 and go to Conditional Formatting -> Manage Rules ... -> Click on the rule -> In the 'Applies to' box you can set the range eg =$J$2:$J$100

 

[iluvsafc]

Sorry i'm still a little confused. So if i go to the 'add a formula section in CF and enter

=AND($J$5:$J$64>TODAY(),$K$4:$K$64="") and change the formatting to red should that then change any cells in J column to red if the date exceeds today but if the cell in column K has a date it wont apply the colour format?

 

[Peter_SSs]

Sorry i'm still a little confused. So if i go to the 'add a formula section in CF and enter

=AND($J$5:$J$64>TODAY(),$K$4:$K$64="") and change the formatting to red should that then change any cells in J column to red if the date exceeds today but if the cell in column K has a date it wont apply the colour format?

No, you have added a whole bunch of $ signs in the formula that mine didn't have. You have also added ranges in the formula that mine didn't have.

Suppose you want this to start in row 4 then select J4:J??
Then in the CF dialog, New Rule, use a formula and use this formula exactly =AND(J4>TODAY(),K4="") & set the format colour
That's it - done.

 

[iluvsafc]

Thanks, but that doesn't seem to work as it formats J cells even if they haven't exceeded TODAY().

 

[Peter_SSs]

Thanks, but that doesn't seem to work as it formats J cells even if they haven't exceeded TODAY().

Perhaps I have mis-interpreted the blue part. Does it work if we turn the inequality sign around (& we would have to add an extra condition that J wasn't blank)?

Excel Workbook
	J	K
3	Review date	Review completed date
4	15/06/2019
5	4/08/2019	4/08/2019
6	28/11/2020
7	28/11/2020	14/11/2019
8	7/07/2019
9
10
CF Dates

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J4	1. / Formula is =AND(J4<>"")	Abc

 

[iluvsafc]

Peter that works brilliantly! Thank you so much for your help!

 

[Peter_SSs]

Peter that works brilliantly! Thank you so much for your help!

You're welcome.