conditional formatting

kevin lazell

Active Member
Joined
Jun 2, 2004
Messages
482
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,800
Office Version
365
Platform
Windows
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

Active Member
Joined
Jun 2, 2004
Messages
482
hi peter
thank you very much for helping with this
but it will not work any ideas please
thank you
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,800
Office Version
365
Platform
Windows
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

Active Member
Joined
Jun 2, 2004
Messages
482
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,800
Office Version
365
Platform
Windows
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

Active Member
Joined
Jun 2, 2004
Messages
482
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,800
Office Version
365
Platform
Windows
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:

kevin lazell

Active Member
Joined
Jun 2, 2004
Messages
482
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

Active Member
Joined
Jun 2, 2004
Messages
482
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
 

Forum statistics

Threads
1,078,437
Messages
5,340,270
Members
399,361
Latest member
Linford

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top