conditional formatting

kevin lazell

Well-known Member
Joined
Jun 2, 2004
Messages
513
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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>
 
Upvote 0
hi peter
thank you very much for helping with this
but it will not work any ideas please
thank you
 
Upvote 0
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. :)
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
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>
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top