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

 

[RAMU]

Mr. Poulsom,

Honestly I did not get that. I dont know whether array formula will work for conditional formatting or not.

Your formula did work fine, I was pleased also, suddenly I found that problem.

Regards
RAMU

 

[Andrew Poulsom]

An array formula will work in conditional formatting. Try it.

 

[RAMU]

Mr. Poulsom,

Tried but not getting desired result. I think Mr. Mark did not use that formula for conditional formatting. I may be wrong, however am not getting result. I have tried in several ways. It may be i am doing something wrong.

Hope one day i shall get my result.

Regards
RAMU

 

[markmzz]

Mr. Poulsom,

Tried but not getting desired result. I think Mr. Mark did not use that formula for conditional formatting. I may be wrong, however am not getting result. I have tried in several ways. It may be i am doing something wrong.

Hope one day i shall get my result.

Regards
RAMU

RAMU,

The formula of column F is the same used in the conditional formating and it work here. Again look at this:

Note: VERDADEIRO = TRUE and FALSO = FALSE.

<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

 

[RAMU]

Dear Mark,

Yes, AWESOME. Only a little modification is required that whenever a data entry will be done that Row will come automatically under the conditional formatting.

Regards
RAMU

 

[markmzz]

Dear Mark,

Yes, AWESOME. Only a little modification is required that whenever a data entry will be done that Row will come automatically under the conditional formatting.

Regards
RAMU

Only modify the range in the box Applies to.

Markmzz

 

[P.Holko]

RAMU, I apologize for the delay. I'm glad you have found a solution.

here is VBA procedure which works as well as conditional formatting

However that code you must assign to button placed on your worksheet or implemented it in procedure triggered by worksheet events.

Sub highlight_it()

Const letterName = "B"
Const letterDate = "A"

Dim i, j, nUnique As Integer
'create an array of unique names
Dim unique As Boolean
lastRow = Range(letterName & "1").End(xlDown).Row
lastColumn = Range("A1").End(xlToRight).Column
ReDim Arr(1 To lastRow, 1 To 4) As Variant
nUnique = 0

'assumed that in first row are placed headlines so:
For i = 2 To lastRow
        unique = True
        For j = 1 To i - 1
            If Arr(j, 1) = Cells(i, letterName).Value Then unique = False
        Next j
        If unique Then
            nUnique = nUnique + 1
            Arr(nUnique, 1) = Cells(i, letterName).Value
            Arr(nUnique, 2) = "1900-01-01"
            Arr(nUnique, 3) = "1900-01-01"
            Arr(nUnique, 4) = 0
        End If
Next i
Arr = WorksheetFunction.Transpose(Arr)
ReDim Preserve Arr(1 To 4, 1 To nUnique)
Arr = WorksheetFunction.Transpose(Arr)

For j = 1 To nUnique
    For i = 2 To lastRow
        If Arr(j, 1) = Cells(i, letterName).Value Then
            Arr(j, 4) = 1 + Arr(j, 4)
            If DateValue(Arr(j, 2)) <= DateValue(Cells(i, letterDate).Value) Then _
                Arr(j, 2) = Cells(i, letterDate).Value
        End If
    Next i
    If Arr(j, 4) > 1 Then
        For i = 2 To lastRow
            If Arr(j, 1) = Cells(i, letterName).Value Then
                If DateValue(Arr(j, 2)) > DateValue(Cells(i, letterDate).Value) _
                And DateValue(Arr(j, 3)) <= DateValue(Cells(i, letterDate).Value) Then
                    Arr(j, 3) = Cells(i, letterDate).Value
                End If
            End If
        Next i
    End If
Next j

For i = 1 To lastRow
        lastColumn = Range(letterName & i).End(xlToRight).Column
        With Range(Cells(i, "A"), Cells(i, lastColumn))
            .Interior.Pattern = xlNone
            .Font.ColorIndex = xlAutomatic
            .Font.TintAndShade = 0
            .Font.Bold = False
        End With
Next i

' highlight entries last entries with interval <=6 month to next entry
For j = 1 To nUnique
    For i = 1 To lastRow
    If Arr(j, 4) > 1 Then
        If VBA.DateValue(Arr(j, 3)) - DateValue(DateSerial(Year(Arr(j, 2)), Month(Arr(j, 2)) - 6, Day(Arr(j, 2)))) > 0 Then
            If Arr(j, 1) = Cells(i, letterName).Value Then
                If Arr(j, 2) = Cells(i, letterDate).Value Then
                lastColumn = Range("A" & i).End(xlToRight).Column
                With Range(Cells(i, "A"), Cells(i, lastColumn))
                    .Interior.Pattern = xlSolid
                    .Interior.PatternColorIndex = xlAutomatic
                    .Interior.Color = 65535
                    .Font.Color = -16776961
                    .Font.Bold = True
                End With
                End If
            End If
        End If
    End If
    Next i
Next j
End Sub

 

[RAMU]

Dear Mr. Volko,

Thanks for your help, but unfortunately neither I know VBA nor I can understand the method that you have mentioned. I only tried to copy paste your code & obviously that does not work.

Can you pls explain me the procedure that I should follow.

Regards
RAMU

 

[P.Holko]

i hope my week English skills do not mislead you.

1. Make sure that you have macros enabled in your worksheet (for details see: http://www.mrexcel.com/forum/showthread.php?t=575536#3)

2. open VBA editor and insert module (for details see: http://www.anthony-vba.kefra.com/vba/vbabasic1.htm)

3. paste my procedure in the module

4. on your sheet with data press the [Alt] and [F8] keys at the same time and on displayed dailog box select "highlight_it" and click button "Run"

This will tell you if and how the procedure works - you should check it in copy of your workbook.

I will think about creating a procedure that will be triggered after you placed a new line to your data.
i do not know how long is your data and your expectation about the time of calculation. If your data is large and the procedure take lots of time it could be better to create macro that will automatically modify the range in the box "Applies to" in the method proposed by markmzz.