conditional formatting

[kevin lazell]

hi everyone
not been on here for a while and am very rusty to say the least
i have 5 consecutive cells say b1:f5 each cell has a date in each date is later than the previous one
when b1 matches todays date then format red
when c1 matches todays date then format yellow and b1 to go back to no format
when d1 matches todays date then format blue and c1 to go back to no format
when e1 matches todays date then format orange and d1 to go back to no format
when f1 matches todays date then format green and e1 to go back to no format
at present it goes down 500 rows and growing
could someone tell me the best way of doing this
many thanks

 

[Peter_SSs]

See if this is what you want. (My dates are in d/m/y format)
- Select B1:Bxx and apply the CF shown for B1
- Select C1:Cxx and apply the CF shown for C1
etc

<b>CF Date</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:83px;" /><col style="width:83px;" /><col style="width:83px;" /><col style="width:83px;" /><col style="width:83px;" /><col style="width:24px;" /><col style="width:25px;" /><col style="width:56px;" /><col style="width:86px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; text-align:right; ">12/11/2019</td><td style="background-color:#ffff00; font-size:10pt; text-align:right; ">13/11/2019</td><td style="font-size:10pt; text-align:right; ">14/11/2019</td><td style="font-size:10pt; text-align:right; ">15/11/2019</td><td style="font-size:10pt; text-align:right; ">16/11/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">Today:</td><td style="font-size:10pt; text-align:right; ">13/11/2019</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">23/10/2019</td><td style="font-size:10pt; text-align:right; ">30/10/2019</td><td style="font-size:10pt; text-align:right; ">6/11/2019</td><td style="background-color:#ffc000; font-size:10pt; text-align:right; ">13/11/2019</td><td style="font-size:10pt; text-align:right; ">20/11/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">5/11/2019</td><td style="font-size:10pt; text-align:right; ">7/11/2019</td><td style="font-size:10pt; text-align:right; ">9/11/2019</td><td style="font-size:10pt; text-align:right; ">11/11/2019</td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">13/11/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">13/09/2019</td><td style="font-size:10pt; text-align:right; ">13/10/2019</td><td style="background-color:#00b0f0; font-size:10pt; text-align:right; ">13/11/2019</td><td style="font-size:10pt; text-align:right; ">13/12/2019</td><td style="font-size:10pt; text-align:right; ">13/01/2020</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">12/08/2019</td><td style="font-size:10pt; text-align:right; ">29/08/2019</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; text-align:right; ">2/10/2019</td><td style="font-size:10pt; text-align:right; ">19/10/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#ff0000; font-size:10pt; text-align:right; ">13/11/2019</td><td style="font-size:10pt; text-align:right; ">18/11/2019</td><td style="font-size:10pt; text-align:right; ">23/11/2019</td><td style="font-size:10pt; text-align:right; ">28/11/2019</td><td style="font-size:10pt; text-align:right; ">3/12/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >J1</td><td >=TODAY()</td></tr></table></td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >B1</td><td >1. / Formula is =B1=$J$1</td><td style="background-color:#ff0000; ">Abc</td></tr><tr><td >C1</td><td >1. / Formula is =C1=$J$1</td><td style="background-color:#ffff00; ">Abc</td></tr><tr><td >D1</td><td >1. / Formula is =D1=$J$1</td><td style="background-color:#00b0f0; ">Abc</td></tr><tr><td >E1</td><td >1. / Formula is =E1=$J$1</td><td style="background-color:#ffc000; ">Abc</td></tr><tr><td >F1</td><td >1. / Formula is =F1=$J$1</td><td style="background-color:#92d050; ">Abc</td></tr></table></td></tr></table>

 

[kevin lazell]

hi peter
thank you very much for helping with this
but it will not work any ideas please
thank you

 

[Peter_SSs]

but it will not work any ideas please

No idea at all unless you explain clearly in what way it "will not work"

1. Is my sample data above unrealistic? (I made it according to "each cell has a date in each date is later than the previous one" as you described.

2. Are my results above incorrect? As I understood the problem the results fit with

when b1 matches todays date then format red
when c1 matches todays date then format yellow and b1 to go back to no format
when d1 matches todays date then format blue and c1 to go back to no format
when e1 matches todays date then format orange and d1 to go back to no format
when f1 matches todays date then format green and e1 to go back to no format

Instead of us guessing what you data is actually like and what the results should be, what about you give us some realistic sample data and explain clearly which cells should be which colour & why? Remember that you know your layout, data and requirements well, but we only know what you tell us or show us.

 

[kevin lazell]

hi peter
when i entered your solution and tried it out nothing worked at all
i changed the first condition by removing the 1./ part so comparing cells b1 and j1 and b1 turned red
as i wanted. the others did not work
basically the 5 columns are different stages of a job ie start date then stage 2 date stage 3 date stage 4 date and completion date
so when a job starts first cell ie b1 turns red and stays red until stage 2 date is reached then c1 turns yellow and then bi goes back to no format
hope this is a bit clearer for you
oh by the way i have tonight upgraded to microsoft proffessional plus 2019
thanks in advance for any help

 

[Peter_SSs]

I may have slightly misinterpreted your requirement & only highlighted a date if it was actually today's date. Instead I think you want to highlight the latest date that is less than or equal to today's date?

Yes, you only enter the actual formula starting at the = sign for each rule. To spell it out in more detail

1. Remove any existing CF by Conditional Formatting - Clear Rules - Clear Rules from Entire Sheet
2. Select B1:Bxx
3. Conditional Formatting - New rule .. - Use a formula to determine which cells to format - Format values where this formula is true: =AND(B1<>"",B1=MAX(IF($B1:$F1<=$J$1,$B1:$F1))) - Format... - Fill tab - Choose colour - OK - OK
4. Select C1:Cxx & repeat step 3 with the next formula and next colour
etc

<b>CF Date (2)</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:83px;" /><col style="width:83px;" /><col style="width:83px;" /><col style="width:83px;" /><col style="width:83px;" /><col style="width:24px;" /><col style="width:25px;" /><col style="width:56px;" /><col style="width:86px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ff0000; font-size:10pt; text-align:right; ">15/11/2019</td><td style="font-size:10pt; text-align:right; ">16/11/2019</td><td style="font-size:10pt; text-align:right; ">17/11/2019</td><td style="font-size:10pt; text-align:right; ">18/11/2019</td><td style="font-size:10pt; text-align:right; ">19/11/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">Today:</td><td style="font-size:10pt; text-align:right; ">15/11/2019</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">26/10/2019</td><td style="font-size:10pt; text-align:right; ">2/11/2019</td><td style="background-color:#00b0f0; font-size:10pt; text-align:right; ">9/11/2019</td><td style="font-size:10pt; text-align:right; ">16/11/2019</td><td style="font-size:10pt; text-align:right; ">23/11/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">8/11/2019</td><td style="font-size:10pt; text-align:right; ">10/11/2019</td><td style="font-size:10pt; text-align:right; ">12/11/2019</td><td style="background-color:#ffc000; font-size:10pt; text-align:right; ">14/11/2019</td><td style="font-size:10pt; text-align:right; ">16/11/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="background-color:#ffff00; font-size:10pt; text-align:right; ">16/10/2019</td><td style="font-size:10pt; text-align:right; ">16/11/2019</td><td style="font-size:10pt; text-align:right; ">16/12/2019</td><td style="font-size:10pt; text-align:right; ">16/01/2020</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">15/08/2019</td><td style="font-size:10pt; text-align:right; ">1/09/2019</td><td style="font-size:10pt; text-align:right; ">18/09/2019</td><td style="font-size:10pt; text-align:right; ">5/10/2019</td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">22/10/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; text-align:right; ">16/11/2019</td><td style="font-size:10pt; text-align:right; ">21/11/2019</td><td style="font-size:10pt; text-align:right; ">26/11/2019</td><td style="font-size:10pt; text-align:right; ">1/12/2019</td><td style="font-size:10pt; text-align:right; ">6/12/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >B1</td><td >1. / Formula is =AND(B1<>"",B1=MAX(IF($B1:$F1<=$J$1,$B1:$F1)))</td><td style="background-color:#ff0000; ">Abc</td></tr><tr><td >C1</td><td >1. / Formula is =AND(C1<>"",C1=MAX(IF($B1:$F1<=$J$1,$B1:$F1)))</td><td style="background-color:#ffff00; ">Abc</td></tr><tr><td >D1</td><td >1. / Formula is =AND(D1<>"",D1=MAX(IF($B1:$F1<=$J$1,$B1:$F1)))</td><td style="background-color:#00b0f0; ">Abc</td></tr><tr><td >E1</td><td >1. / Formula is =AND(E1<>"",E1=MAX(IF($B1:$F1<=$J$1,$B1:$F1)))</td><td style="background-color:#ffc000; ">Abc</td></tr><tr><td >F1</td><td >1. / Formula is =AND(F1<>"",F1=MAX(IF($B1:$F1<=$J$1,$B1:$F1)))</td><td style="background-color:#92d050; ">Abc</td></tr></table></td></tr></table>

 

[kevin lazell]

hi peter
thank you very much for your effort you almost had it right
where you had $b1:$f1<=$j$1 i removed the< and it worked
perfectly great stuff
thank you once again

 

[Peter_SSs]

hi peter
thank you very much for your effort you almost had it right
where you had $b1:$f1<=$j$1 i removed the< and it worked
perfectly great stuff
thank you once again

In that case you are now using a much more complicated CF than required, as what you have now results exactly the same as the CF I suggested in post 2 (see below), which you just must have applied incorrectly at the time.

I would suggest making a copy of the sheet/book and trying that very simple CF again & compare the results.
If you think it does not do the same job, can you give an example of the dates in the 'wrong' row and the date in J1 and explain what is highlighted (or not highlighted) incorrectly?

Post 2 CF:

<b>CF Date</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:83px;" /><col style="width:83px;" /><col style="width:83px;" /><col style="width:83px;" /><col style="width:83px;" /><col style="width:24px;" /><col style="width:25px;" /><col style="width:56px;" /><col style="width:86px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; text-align:right; ">15/11/2019</td><td style="background-color:#ffff00; font-size:10pt; text-align:right; ">16/11/2019</td><td style="font-size:10pt; text-align:right; ">17/11/2019</td><td style="font-size:10pt; text-align:right; ">18/11/2019</td><td style="font-size:10pt; text-align:right; ">19/11/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">Today:</td><td style="font-size:10pt; text-align:right; ">16/11/2019</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">26/10/2019</td><td style="font-size:10pt; text-align:right; ">2/11/2019</td><td style="font-size:10pt; text-align:right; ">9/11/2019</td><td style="background-color:#ffc000; font-size:10pt; text-align:right; ">16/11/2019</td><td style="font-size:10pt; text-align:right; ">23/11/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">8/11/2019</td><td style="font-size:10pt; text-align:right; ">10/11/2019</td><td style="font-size:10pt; text-align:right; ">12/11/2019</td><td style="font-size:10pt; text-align:right; ">14/11/2019</td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">16/11/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="font-size:10pt; text-align:right; ">16/10/2019</td><td style="background-color:#00b0f0; font-size:10pt; text-align:right; ">16/11/2019</td><td style="font-size:10pt; text-align:right; ">16/12/2019</td><td style="font-size:10pt; text-align:right; ">16/01/2020</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">15/08/2019</td><td style="font-size:10pt; text-align:right; ">1/09/2019</td><td style="font-size:10pt; text-align:right; ">18/09/2019</td><td style="font-size:10pt; text-align:right; ">5/10/2019</td><td style="font-size:10pt; text-align:right; ">22/10/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#ff0000; font-size:10pt; text-align:right; ">16/11/2019</td><td style="font-size:10pt; text-align:right; ">21/11/2019</td><td style="font-size:10pt; text-align:right; ">26/11/2019</td><td style="font-size:10pt; text-align:right; ">1/12/2019</td><td style="font-size:10pt; text-align:right; ">6/12/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >B1</td><td >1. / Formula is =B1=$J$1</td><td style="background-color:#ff0000; ">Abc</td></tr></table></td></tr></table>


Post 6 CF with your adjustment:

<b>CF Date (3)</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:83px;" /><col style="width:83px;" /><col style="width:83px;" /><col style="width:83px;" /><col style="width:83px;" /><col style="width:24px;" /><col style="width:25px;" /><col style="width:56px;" /><col style="width:86px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; text-align:right; ">15/11/2019</td><td style="background-color:#ffff00; font-size:10pt; text-align:right; ">16/11/2019</td><td style="font-size:10pt; text-align:right; ">17/11/2019</td><td style="font-size:10pt; text-align:right; ">18/11/2019</td><td style="font-size:10pt; text-align:right; ">19/11/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">Today:</td><td style="font-size:10pt; text-align:right; ">16/11/2019</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">26/10/2019</td><td style="font-size:10pt; text-align:right; ">2/11/2019</td><td style="font-size:10pt; text-align:right; ">9/11/2019</td><td style="background-color:#ffc000; font-size:10pt; text-align:right; ">16/11/2019</td><td style="font-size:10pt; text-align:right; ">23/11/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">8/11/2019</td><td style="font-size:10pt; text-align:right; ">10/11/2019</td><td style="font-size:10pt; text-align:right; ">12/11/2019</td><td style="font-size:10pt; text-align:right; ">14/11/2019</td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">16/11/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="font-size:10pt; text-align:right; ">16/10/2019</td><td style="background-color:#00b0f0; font-size:10pt; text-align:right; ">16/11/2019</td><td style="font-size:10pt; text-align:right; ">16/12/2019</td><td style="font-size:10pt; text-align:right; ">16/01/2020</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">15/08/2019</td><td style="font-size:10pt; text-align:right; ">1/09/2019</td><td style="font-size:10pt; text-align:right; ">18/09/2019</td><td style="font-size:10pt; text-align:right; ">5/10/2019</td><td style="font-size:10pt; text-align:right; ">22/10/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#ff0000; font-size:10pt; text-align:right; ">16/11/2019</td><td style="font-size:10pt; text-align:right; ">21/11/2019</td><td style="font-size:10pt; text-align:right; ">26/11/2019</td><td style="font-size:10pt; text-align:right; ">1/12/2019</td><td style="font-size:10pt; text-align:right; ">6/12/2019</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >B1</td><td >1. / Formula is =AND(B1<>"",B1=MAX(IF($B1:$F1=$J$1,$B1:$F1)))</td><td style="background-color:#ff0000; ">Abc</td></tr></table></td></tr></table>

 

[kevin lazell]

sorry for late reply peter i have been away for a few weeks on a film location
you will be pleased to learn that i have got it working
thank you very much for your efforts

 

[kevin lazell]

hi peter ss
you kindly provided me with this formula =AND(B1<>"",B1=MAX(IF($B1:$F1=$J$1,$B1:$F1)))
coluld please break it down for me and explain to me how it works and also show me what part of the formula
actually removes the format from the previous cell a1
thanks in advance kevin