Formula or VBA Help

R

RAMU

Active Member
Joined
Dec 11, 2009
Messages
345
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 85px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">DATE</TD><TD style="TEXT-ALIGN: center">NAME</TD><TD style="TEXT-ALIGN: center">ITEM</TD><TD style="TEXT-ALIGN: center">QTY</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">1-Sep-11</TD><TD>SANDY</TD><TD>FAN</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">1-Jan-12</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; BACKGROUND-COLOR: #ffff00">SANDY</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; BACKGROUND-COLOR: #ffff00">FAN</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">1</TD></TR></TBODY></TABLE>


Dear All,

I have been facing a problem since last nite with this workbook.
I want if within next 6 months the same material is issued to the same person since the last issue then A3 to D3 will be filled by Yellow, fonts will be red & Bold.
Here 6 months does not mean 180 days. Suppose a material was issued to "ABC" on 24th July 2011, now if the same material is issued to "ABC" within 23rd January 2012, then the cells & font will be coloure & bold otherwise normal.
It is a continuous process & will be done in regular basis, hence rows will be continuously added.
One matter that should have to be counted that every calculation will be done from the last issue (same person & same item )

Pls provide me a formula or VBA Code using which my problem can be solved.

Any help would be highly appreciated.
N.B. I have few data against previous issues, therefore when I shall copy paste those in my new workbook my desired result or output should reflect.

Regards
RAMU
 
Sort by date Sort by votes
Another way (DATE function):

IF(SUMPRODUCT(--($B$2:$B2=$B2),--($C$2:$C2=$C2))>1,
DATE(YEAR(INDEX($A$2:$A2,LARGE((($B$2:$B2=$B2)*($C$2:$C2=$C2)*(ROW($B$2:$B2)-ROW($B$1))),2))),
MONTH(INDEX($A$2:$A2,LARGE((($B$2:$B2=$B2)*($C$2:$C2=$C2)*(ROW($B$2:$B2)-ROW($B$1))),2)))+6,
DAY(INDEX($A$2:$A2,LARGE((($B$2:$B2=$B2)*($C$2:$C2=$C2)*(ROW($B$2:$B2)-ROW($B$1))),2))))>=
INDEX($A$2:$A2,LARGE((($B$2:$B2=$B2)*($C$2:$C2=$C2)*(ROW($B$2:$B2)-ROW($B$1))),1)))}

Markmzz
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 68px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>DATE</TD><TD>NAME</TD><TD>ITEM</TD><TD>QTY</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">11-Jul-11</TD><TD>SANDIP</TD><TD>FAN</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">9-Aug-11</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; BACKGROUND-COLOR: #ffff00">sandip</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; BACKGROUND-COLOR: #ffff00">fan</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">8-Feb-12</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; BACKGROUND-COLOR: #ffff00">sandip</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; BACKGROUND-COLOR: #ffff00">fan</TD><TD style="TEXT-ALIGN: right">5</TD></TR></TBODY></TABLE>

As per your instruction I selected A3:D3 & followed the step of conditional formatting in Excel 2007.

But when I am going to add another data in ROW 4, then this result is coming. I want to say that it is a continuous process, therefore data entries will be done on regular basis. For that when the condition will be fufilled that row will be highlighted totally not partly. Here My expected result is highlighting should be from A4 to D4 but it is done only in two cells.

Regards
RAMU
 
Upvote 0
Result when my formula is applied to the range A3:D4:

<TABLE class=MsoNormalTable style="MARGIN: auto auto auto 4.65pt; WIDTH: 181.85pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" cellSpacing=0 cellPadding=0 width=242 border=0><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 72.75pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .5pt" vAlign=bottom noWrap width=97>DATE<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 42.15pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=56>NAME<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 35.95pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=48>ITEM<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 31pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=41>QTY<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 72.75pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=bottom noWrap width=97>
11-Jul-11<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 42.15pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=56>SANDIP<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 35.95pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=48>FAN<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 31pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=41 x:num>
1<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: yellow; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 72.75pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=bottom noWrap width=97>
09-Aug-11<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: yellow; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 42.15pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=bottom noWrap width=56>sandip<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: yellow; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 35.95pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=bottom noWrap width=48>fan<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: yellow; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 31pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=bottom noWrap width=41 x:num>
2<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: yellow; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 72.75pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=bottom noWrap width=97>
08-Feb-12<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: yellow; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 42.15pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=bottom noWrap width=56>sandip<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: yellow; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 35.95pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=bottom noWrap width=48>fan<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: yellow; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 31pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=bottom noWrap width=41 x:num>
5<o:p></o:p>​
</TD></TR></TBODY></TABLE>

Result when a day is added to the date in A4:

<TABLE class=MsoNormalTable style="MARGIN: auto auto auto 4.65pt; WIDTH: 181.85pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" cellSpacing=0 cellPadding=0 width=242 border=0><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 72.75pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .5pt" vAlign=bottom noWrap width=97>DATE<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 42.15pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=56>NAME<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 35.95pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=48>ITEM<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 31pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=41>QTY<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 72.75pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=bottom noWrap width=97>
11-Jul-11<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 42.15pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=56>SANDIP<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 35.95pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=48>FAN<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 31pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=41 x:num>
1<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: yellow; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 72.75pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=bottom noWrap width=97>
09-Aug-11<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: yellow; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 42.15pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=bottom noWrap width=56>sandip<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: yellow; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 35.95pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=bottom noWrap width=48>fan<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: yellow; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 31pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=bottom noWrap width=41 x:num>
2<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 72.75pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=bottom noWrap width=97>
09-Feb-12<o:p></o:p>​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 42.15pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=56>sandip<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 35.95pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=48>fan<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #f0f0f0; WIDTH: 31pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=41 x:num>
5<o:p></o:p>​
</TD></TR></TBODY></TABLE>
 
Upvote 0
Mr. Poulsom,

I cant understand the problem, same result is showing. Am I doing anything wrong ? Might be. See I am using Excel 2007. There selecting A3:D3 I am going to conditional formatting. Under that tab going to new formatting rule. Then use a formula to determine which cells to format.

There I had put your last formula "=AND($A3<DATE(YEAR($A2),MONTH($A2)+6,DAY($A2)),$B3=$B2,$C3=$C2)" & formatted.

Is it wrong process ?

Please confirm.

Regards
RAMU
 
Upvote 0
I confirm the formula I used for A3:D4:

=AND($A3 < DATE(YEAR($A2),MONTH($A2)+6,DAY($A2)),$B3=$B2,$C3=$C2)<DATE(YEAR($A2),MONTH($A2)+6,DAY($A2)),$B3=$B2,$C3=$C2)[ p html]<>
 
Upvote 0
RAMU,

Try this new formula for Excel 2007 and tell me if it work.

Here it work. Look at this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">DATE</td><td style="text-align: center;;">NAME</td><td style="text-align: center;;">ITEM</td><td style="text-align: center;;">QTY</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">CF</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">7/11/2011</td><td style="text-align: center;;">SANDIP</td><td style="text-align: center;;">FAN</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">8/9/2011</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">sandip</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">fan</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">2</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">VERDADEIRO</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">2/8/2012</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">sandip</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">fan</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">5</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">VERDADEIRO</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">7/3/2012</td><td style="text-align: center;;">SANDY</td><td style="text-align: center;;">FAN</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">7/4/2012</td><td style="text-align: center;;">PAUL</td><td style="text-align: center;;">FAN</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">7/5/2012</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">SANDY</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">FAN</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">5</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">VERDADEIRO</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">1/6/2013</td><td style="text-align: center;;">SANDY</td><td style="text-align: center;;">FAN</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet7</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">{=IF(<font color="Blue">COUNTIFS(<font color="Red">$B$2:$B2,$B2,$C$2:$C2,$C2</font>)>1,
EDATE(<font color="Red">INDEX(<font color="Green">$A$2:$A2,LARGE(<font color="Purple">(<font color="Teal">$B$2:$B2=$B2</font>)*(<font color="Teal">$C$2:$C2=$C2</font>)*(<font color="Teal">ROW(<font color="#FF00FF">$B$2:$B2</font>)-ROW(<font color="#FF00FF">$B$1</font>)</font>),2</font>)</font>),6</font>)>
INDEX(<font color="Red">$A$2:$A2,LARGE(<font color="Green">(<font color="Purple">$B$2:$B2=$B2</font>)*(<font color="Purple">$C$2:$C2=$C2</font>)*(<font color="Purple">ROW(<font color="Teal">$B$2:$B2</font>)-ROW(<font color="Teal">$B$1</font>)</font>),1</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz
 
Upvote 0
markmzz said:
RAMU,

Try this new formula for Excel 2007 and tell me if it work.

Here it work. Look at this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">DATE</td><td style="text-align: center;;">NAME</td><td style="text-align: center;;">ITEM</td><td style="text-align: center;;">QTY</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">CF</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">7/11/2011</td><td style="text-align: center;;">SANDIP</td><td style="text-align: center;;">FAN</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">8/9/2011</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">sandip</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">fan</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">2</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">VERDADEIRO</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">2/8/2012</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">sandip</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">fan</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">5</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">VERDADEIRO</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">7/3/2012</td><td style="text-align: center;;">SANDY</td><td style="text-align: center;;">FAN</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">7/4/2012</td><td style="text-align: center;;">PAUL</td><td style="text-align: center;;">FAN</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">7/5/2012</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">SANDY</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">FAN</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">5</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">VERDADEIRO</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">1/6/2013</td><td style="text-align: center;;">SANDY</td><td style="text-align: center;;">FAN</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet7</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">{=IF(<font color="Blue">COUNTIFS(<font color="Red">$B$2:$B2,$B2,$C$2:$C2,$C2</font>)>1,
EDATE(<font color="Red">INDEX(<font color="Green">$A$2:$A2,LARGE(<font color="Purple">(<font color="Teal">$B$2:$B2=$B2</font>)*(<font color="Teal">$C$2:$C2=$C2</font>)*(<font color="Teal">ROW(<font color="#FF00FF">$B$2:$B2</font>)-ROW(<font color="#FF00FF">$B$1</font>)</font>),2</font>)</font>),6</font>)>
INDEX(<font color="Red">$A$2:$A2,LARGE(<font color="Green">(<font color="Purple">$B$2:$B2=$B2</font>)*(<font color="Purple">$C$2:$C2=$C2</font>)*(<font color="Purple">ROW(<font color="Teal">$B$2:$B2</font>)-ROW(<font color="Teal">$B$1</font>)</font>),1</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz
Click to expand...

Dear Mark,

Thanks a lot for your formula but here what is the function of F column, you mean I have to use the formula in F column. Then ? How the entries would be highlighted if the condition fulfill ?

Also you mentioned CF. What conditional formatting you used & how ?
pls help

Regards
Ramu
 
Upvote 0
Andrew Poulsom said:
I confirm the formula I used for A3:D4:

=AND($A3 < DATE(YEAR($A2),MONTH($A2)+6,DAY($A2)),$B3=$B2,$C3=$C2)<DATE(YEAR($A2),MONTH($A2)+6,DAY($A2)),$B3=$B2,$C3=$C2)[ html]< p>
Click to expand...

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 68px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">DATE</TD><TD style="TEXT-ALIGN: center">NAME</TD><TD style="TEXT-ALIGN: center">ITEM</TD><TD style="TEXT-ALIGN: center">QTY</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">25-Jul-11</TD><TD>SANDY</TD><TD>FAN</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">25-Jul-11</TD><TD>PAUL</TD><TD>CFL</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">25-Jul-11</TD><TD>SANDY</TD><TD>LUGS</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">25-Jul-11</TD><TD>PAUL</TD><TD>CFL</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: right">26-Jul-11</TD><TD style="BACKGROUND-COLOR: #ece9d8">SANDY</TD><TD style="BACKGROUND-COLOR: #ece9d8">LIGHT</TD><TD style="BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">28-Jul-11</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; BACKGROUND-COLOR: #ffff00">SANDY</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; BACKGROUND-COLOR: #ffff00">LIGHT</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">29-Jul-11</TD><TD style="BACKGROUND-COLOR: #ece9d8">SANDY</TD><TD style="BACKGROUND-COLOR: #ece9d8">FAN</TD><TD style="BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">30-Jul-11</TD><TD style="BACKGROUND-COLOR: #ece9d8">SANDY</TD><TD style="BACKGROUND-COLOR: #ece9d8">LIGHT</TD><TD style="BACKGROUND-COLOR: #ece9d8; TEXT-ALIGN: right">1</TD></TR></TBODY></TABLE>

Mr. Poulsom,
Using your formula I am facing a problem, it can't calculate all previous issues, if there is a break of different item or different person, condition formatting is not working.

I mentioned that if the same item is issued to the same person since last issue of that item to the same person does not cross 6 months then that entry would be highlighted.

Regards
RAMU
 
Upvote 0
Dear All,

I think VBA code may sort out my problem.
Can anyone plz help me ?

I have become crazy.

Pls help.

Regards
RAMU
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
624
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
D
  • Solved
Re-distributing numbers against a total value.
Replies
6
Views
251
DazCD
D
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
90
Dazzybeeguy
D
E
  • Question Question
Adding a custom message.
Replies
1
Views
666
Micron
M

Forum statistics

Threads
1,224,609
Messages
6,179,876
Members
452,949
Latest member
Dupuhini

Share this page

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