Formula or VBA Help

[RAMU]

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

 

[P.Holko]

Hi, I've in mind only one solution: VBA procedure which will run at worksheet event (past new row). for format duplicate entries you may adopt my code placed at this thread
http://www.mrexcel.com/forum/showthread.php?t=575808 #4

 

[RAMU]

I know nothing about VBA. will it work if I just copy paste ?

 

[P.Holko]

no. it should be modified. soon I'll try to write a proper script. if you can give me details about this - when should be runs, what events should trigger the procedure (sheet activation, pasting a new row...), how long is your table, which format has "A" column (date?)

 

[RAMU]

no. it should be modified. soon I'll try to write a proper script. if you can give me details about this - when should be runs, what events should trigger the procedure (sheet activation, pasting a new row...), how long is your table, which format has "A" column (date?)

Hello,

Yes, Column A is date.

Table have been long enough as it is a continuous process, whenever a material will be issued to a person that entry will be done on that particular day.

I cant get about sheet activation, see I have few previous data which I shall copy from that file & paste here. And subsequently new entries will be done on daily basis but the conditions will remain same that if the same item is issued to the same person within next 6 months since last issuing date of the same item to same person then that ROW (here suppose A3 to D3) will be highlighted in that pattern that I have shown in my desired result.

Regards
Ramu

 

[RAMU]

Please help to sort out the issue.

Regards
RAMU

 

[Andrew Poulsom]

Select A3:D3 and choose Format|Conditional Formatting from the menu. Condition 1 Formula is:

=AND($A3<=DATE(YEAR($A2),MONTH($A2)+6,DAY($A2)),$B3=$B2,$C3=$C2)

click Format, choose one and click OK twice. Result with your sample data:

<TABLE class=MsoNormalTable style="MARGIN: auto auto auto 4.65pt; BORDER-COLLAPSE: collapse; mso-table-layout-alt: fixed; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" cellSpacing=0 cellPadding=0 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: 81.75pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .5pt" vAlign=bottom width=109>

DATE<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>​

</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: 54pt; 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 width=72>

NAME<o></o>​

</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: 45pt; 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 width=60>

ITEM<o></o>​

</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: 36pt; 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 width=48>

QTY<o></o>​

</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: 81.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 width=109>

01-Sep-11<o></o>​

</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: 54pt; 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 width=72>SANDY<o></o>
</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: 45pt; 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 width=60>FAN<o></o>
</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: 36pt; 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 width=48 x:num>

1<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2; 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: 81.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 width=109>

01-Jan-12<o></o>​

</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: 54pt; 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 width=72>SANDY<o></o>
</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: 45pt; 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 width=60>FAN<o></o>
</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: 36pt; 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 width=48 x:num>

1<o></o>​

</TD></TR></TBODY></TABLE>

 

[RAMU]

Mr. Poulsom,

Yes, fantastic, little problem is that if today i.e. 1st Sept 2011 the item is issued then till 29th Feb 2012 will be its condition period. He will be eligible for next issue on 1st March 2012. But here 1st March 2012 also counting & highlighting.

Second, it is my continuous process not only for A3 to D3. Every multiple nos of data entries will be done, In this case I was trying to select A3:D170 & using conditional formatting. Result is Except A3 to D3 others cells are filling up with yellow colour.

Please help to sort out.

Also help me to make this conditional formatting for infinite nos of rows, if possible.

Regards
RAMU

 

[markmzz]

Maybe 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;;">9/1/2011</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;">3</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">1/1/2012</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">PAUL</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;;">3</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="text-align: center;;">7/2/2012</td><td style="text-align: center;;">SANDY</td><td style="text-align: center;;">FAN</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;text-align: center;color: #FF0000;background-color: #FFFF00;;">7/3/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;;">3</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;">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">Sheet6</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">SUMPRODUCT(<font color="Red">--(<font color="Green">$B$2:$B2=$B2</font>),--(<font color="Green">$C$2:$C2=$C2</font>)</font>)>1,EDATE(<font color="Red">INDEX(<font color="Green">$A$2:$A2,LARGE(<font color="Purple">(<font color="Teal">(<font color="#FF00FF">$B$2:$B2=$B2</font>)*(<font color="#FF00FF">$C$2:$C2=$C2</font>)*(<font color="#FF00FF">ROW(<font color="Navy">$B$2:$B2</font>)-ROW(<font color="Navy">$B$1</font>)</font>)</font>),2</font>)</font>),6</font>)>=INDEX(<font color="Red">$A$2:$A2,LARGE(<font color="Green">(<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>)</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

 

[Andrew Poulsom]

For your first question change the formula to:

=AND($A3<DATE(YEAR($A2),MONTH($A2)+6,DAY($A2)),$B3=$B2,$C3=$C2)

For your second question please post a sample of your data with the expected results.