Conditional formatting category and date dependent

[James2015]

Hi,

This is my first post, so apologies if i've started a thread in the wrong place.

I need to use a conditional formatting fomula, which checks a category (e.g. dog or cat for the purpose of example) in column B (ideally all cells as the data is contantly being added), and if the date in column D, is occuring in the next 10 days the applicable date cell/s in column D turn Red or another colour of my choosing.

The closest i've got is:

=AND(+$S:$S>TODAY()-10,SEARCH("Dog",$B1)>0)

I'd attach an example but alas I am not sure how.


- Yours,

Grateful Amateur

 

[oldbrewer]

[TABLE="width: 939"]
<colgroup><col><col><col span="6"><col><col><col span="4"></colgroup><tbody>[TR]
[TD]animal[/TD]
[TD]date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]today[/TD]
[TD="align: right"]20/04/2015[/TD]
[TD="colspan: 4"]<<<<<<<<<<<<<<this is cell J1[/TD]
[/TR]
[TR]
[TD]cat[/TD]
[TD="align: right"]21/04/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]plus 10 days[/TD]
[TD="align: right"]30/04/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]dog[/TD]
[TD="align: right"]22/04/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]snake[/TD]
[TD="align: right"]23/04/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cat[/TD]
[TD="align: right"]24/04/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]dog[/TD]
[TD="align: right"]25/04/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]snake[/TD]
[TD="align: right"]26/04/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cat[/TD]
[TD="align: right"]27/04/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]dog[/TD]
[TD="align: right"]28/04/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]snake[/TD]
[TD="align: right"]29/04/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cat[/TD]
[TD="align: right"]30/04/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]dog[/TD]
[TD="align: right"]01/05/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]snake[/TD]
[TD="align: right"]02/05/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cat[/TD]
[TD="align: right"]03/05/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]dog[/TD]
[TD="align: right"]04/05/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]snake[/TD]
[TD="align: right"]05/05/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cat[/TD]
[TD="align: right"]06/05/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]dog[/TD]
[TD="align: right"]07/05/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]snake[/TD]
[TD="align: right"]08/05/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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]
[TD][/TD]
[TD][/TD]
[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]
[TD][/TD]
[TD][/TD]
[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]
[TD][/TD]
[TD][/TD]
[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]
[TD][/TD]
[TD][/TD]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]this conditional format formula[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]turns dates in B3,B6 and B9 red - honest !!!![/TD]
[TD][/TD]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]in B3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]=AND(A3="dog",B3>$J$1,B3<($J$2+1))=TRUE[/TD]
[TD][/TD]
[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]
[TD][/TD]
[TD][/TD]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[James2015]

Thanks for getting back to me, i'll give this a go tomorrow, if it doesn't quite work for my situation how to I attach an example? Or is it easier to draw it out in rows and columns on here?

 

[oldbrewer]

just copy and paste a bit of your spreadsheet

 

[James2015]

I think you method would work for this but is there a way to do it without the formula relying on the Today and + 10 day cells? Essentially having the +10 and Today within the conditional formatting formula? Thanks for your help so far .

[TABLE="width: 160"]
<TBODY>[TR]
[TD]RMS / CMS</SPAN>[/TD]
[TD]Day 100</SPAN>[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CMS</SPAN>[/TD]
[TD]01/11/2013</SPAN>[/TD]
[/TR]
[TR]
[TD]CMS</SPAN>[/TD]
[TD]05/07/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]CMS</SPAN>[/TD]
[TD]07/08/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]CMS</SPAN>[/TD]
[TD]03/09/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]CMS</SPAN>[/TD]
[TD]24/09/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]RMS </SPAN>[/TD]
[TD]03/09/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]CMS</SPAN>[/TD]
[TD="align: right"]18/04/2013</SPAN>[/TD]
[/TR]
[TR]
[TD]RMS </SPAN>[/TD]
[TD]02/04/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]CMS</SPAN>[/TD]
[TD]20/07/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]CMS</SPAN>[/TD]
[TD]24/09/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]CMS</SPAN>[/TD]
[TD]09/10/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]CMS</SPAN>[/TD]
[TD]12/10/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]CMS</SPAN>[/TD]
[TD]26/10/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]CMS</SPAN>[/TD]
[TD]06/11/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]CMS</SPAN>[/TD]
[TD]19/11/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]CMS</SPAN>[/TD]
[TD]26/11/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]CMS</SPAN>[/TD]
[TD]26/11/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]CMS</SPAN>[/TD]
[TD]05/12/2014</SPAN>[/TD]
[/TR]
[TR]
[TD]CMS</SPAN>[/TD]
[TD]02/01/2015</SPAN>[/TD]
[/TR]
[TR]
[TD]RMS </SPAN>[/TD]
[TD]08/01/2015</SPAN>[/TD]
[/TR]
[TR]
[TD]RMS </SPAN>[/TD]
[TD]18/01/2015</SPAN>[/TD]
[/TR]
[TR]
[TD]RMS </SPAN>[/TD]
[TD]08/02/2015</SPAN>[/TD]
[/TR]
[TR]
[TD]CMS</SPAN>[/TD]
[TD]13/02/2015</SPAN>[/TD]
[/TR]
[TR]
[TD]RMS </SPAN>[/TD]
[TD]27/02/2015</SPAN>[/TD]
[/TR]
[TR]
[TD]CMS</SPAN>[/TD]
[TD]20/03/2015</SPAN>[/TD]
[/TR]
[TR]
[TD]RMS </SPAN>[/TD]
[TD]20/03/2015</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL span=2></COLGROUP>[/TABLE]

 

[oldbrewer]

yes instead of the today() cell (J1) put today() in the formula likewise instead of J2 put today() + 11

 

[James2015]

I'm not sure why but it won't let me most the formula which worked in full?

<<<< =AND(B1="CMS",M1>TODAY(),M1<TODAY()+10)=TRUE >>>

Last attempt but either way what you said worked so a big thanks!

 

[oldbrewer]

conditional formatting can be very trying at times, open a new spreadsheet, put a date into A1 and try to turn it red if it is between tomorrow and tomorrow plus 10 days.....you will get it within 5 minutes.........