Compare each Cell in Columns with some alternates & format cell, if condition is met -Progressive Scan

[ANALYSTBANK]

My current data sheet looks as under;


[TABLE="width: 679"]
<tbody>[TR]
[TD]Row[/TD]
[TD]ColumnK
(Col Index 11)[/TD]
[TD]ColumnL
(Col Index 12)[/TD]
[TD]ColumnM
(Col Index 13)[/TD]
[TD]ColumnN
(Col Index 14)[/TD]
[TD]ColumnO
(Col Index 15)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Header[/TD]
[TD]Header[/TD]
[TD]Header[/TD]
[TD]Header[/TD]
[TD]Header[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]21-Sep-2013[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]27-Sep-2013[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]03-Oct-2013[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11-Oct-2013[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]23-Oct-2013[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]17-Sep-2013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01-Oct-2013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]08-Oct-2013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]23-Oct-2013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]29-Sep-2013[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD]11-Oct-2013[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD]13-Oct-2013[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]20-Oct-2013[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD]24-Sep-2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD]21-Oct-2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]21-Sep-2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]23-Sep-2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]02-Oct-2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]13-Oct-2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]22-Oct-2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]26-Oct-2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I wanted a macro that would start reading each cells in Column K from Cell K3 (till last row say K100), and do following;

1) If cell is Empty in ColumnK (say K3), move down to next cell (say K4) in ColumnK
2) When Data is found in above case, say K18, it should compare
either a) Exact date (i.e. 21Sep13), or
b) 1 day after (22Sep13), or
c) 2 day after (23Sep13) OR
d) 1 day before (20Sep13) , or
e) 2 day before (19Sep13)

in each cell in Column L.

When date is found per above criteria in ColumnL, the cell should be highlighted either byCOLOR or BOLD

3) Then counter should is moved down to Cell K19 (immediate cell below K18) and repeat the process as given in 2

4) This process should be repeated till Cell K100 which is the last range.

For illustration purpose, I’ve manually highlighted Cell K22 (Date 22Oct13) and L17 (having date 21Oct13), which is just one day before as explained in 2(d) above.

There could be case, where cell has date that meets criteria more than once, I guess, that should not be the problem, as formatting (COLOR or BOLD) could be overwritten, and last hit will always be highlighted.

So, the first comparison was between Column K and Column L, and that is where I sought help, and @KevatArvind was kind enough to provide precise solution, as under. His solution ensured that process, later compared between Column L with Column M, and then Column M with Column N and so on, as I had asked for.

Option Explicit
Sub Compare_Column()
Dim i, j, k, l, x As Integer
Dim lr, lastcol, lr1 As Long
Dim cell As Range
lastcol = 14 ' Change The No 14 To other 14 will cover till Column "O" so if you need to compare more column just change the no 15,16..so on or whatever you want
Application.ScreenUpdating = False
For j = 11 To lastcol
    lr = Cells(Rows.Count, j).End(xlUp).Row
    For i = 3 To lr
        For k = 3 To lr
            If Cells(i, j) <> "" And IsDate(Cells(i, j)) Then
                If Cells(i, j) - Cells(k, j + 1) <= 2 And Cells(i, j) - Cells(k, j + 1) >= -2 Then
                    Cells(i, j).Interior.ColorIndex = 6
                    Cells(i, j).Font.Bold = True
                    Cells(k, j + 1).Interior.ColorIndex = 8
                    Cells(k, j + 1).Font.Bold = True
                End If
            End If
        Next
    Next
Next
Application.ScreenUpdating = True
End Sub

In the above solution, what is happening is that (as I’d asked) comparison is progressive i.e. Comparison is between Col K and Col L, Col L and Col M, Col M and Col N and so on till Column V (Column Index22).

So, No comparison is made between Column K with Column M, N, O, and onward till Col V, Column L with Column N, O, P and so on till Col V.

Now, how to change the code which can compare the cell content for criteria defined above in following manner;

Comparing each cell (progressive scan starting with column K – i.e. Col Index 11)
Column K with Column L;
Column K with Column M;
Column K with Column N;
And so on till Column V (Column Index 22),

Once this is done

Start Comparing each cell from
Column L with Column M
Column L with Column N
Column L with Column O
And so on till Column V (Column Index 22)

Then

Column M with Column N
Column M with Column O
Column M with Column P
And so on till Column V (Column Index 22)

Lastly
Column U with Column V

Regards

 

[ANALYSTBANK]

How can i delete this thread, or should I put query differently here only?

 

[Aladin Akyurek]

How can i delete this thread, or should I put query differently here only?

The latter...

 

[ANALYSTBANK]

I have worksheet which has Data Range (D3:V100). Data type in this range is DATE ONLY . Row 1 to 2 is used as HEADER, and should not be disturbed.

Row data in each column could vary from column to column, e.g. some column might have data only upto 7 row, some column might have data only upto 12 rows or only 5 row and so on, and some column may just be blank only

Now, Macro should start reading (for comparison) from cell D3,
1) Compare the date value in Cell D3 with each cell in Column E, Say, E3,E4,E5,E6 (till last data in Column E) – xldown.

2) If the gap between these date is 'equal or +- 2 days' then macro should highlight (Bold+Color) the match date in Column E, else do nothing.

3) Once all data comparision is made in Column E considering D3 as base, it should repeat same process from next Column i.e Column F, say, and Compare D3 with F3,F4,F5 till end.

4) And this process should continue with each cell in Column G, then Column H, till last column V.

5) Now the counter be moved down to cell D4, and compare its cell value ‘date’ with Each Cell in Column E, say, E3,E4,E5, till data found and repeat the process as mentioned in Point 3), and 4) above

6) Once each cell from Column D is evaluated against each cell in column E, column F, till Column V, now comparison be made from Column E

7) Now, Starting with Cell E3, process mentioned in Point 1 to 6 be re-iterated with subsequent columns F, G till Column V

8) Then do same thing starting with Column F (Cell F3) with other columns till end

At the end, sheet would have highlighted and non-highlighted cells only. Because, non highlighted cells are of no use to me, same be deleted/cleared.

The output should look like below. I have tried using different color to give you feel, what I mean and said above.

Unformatted (nonbold, no filled) cells content may be cleared/deleted.

 

[Aladin Akyurek]

Another suggestion. Images are not readable into Excel. Any helper would be forced to retype everything. Try to use amethod that works:

Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste

 

[ANALYSTBANK]

[TABLE="width: 661"]
<tbody>[TR]
[TD="align: right"]01-Oct-2013[/TD]
[TD="align: right"]12-Oct-2013[/TD]
[TD="align: right"]29-Sep-2013[/TD]
[TD="align: right"]05-Oct-2013[/TD]
[TD="align: right"]21-Oct-2013[/TD]
[TD="align: right"]28-Oct-2013[/TD]
[TD="align: right"]11-Oct-2013[/TD]
[TD="align: right"]30-Sep-2013[/TD]
[TD="align: right"]02-Oct-2013[/TD]
[TD="align: right"]07-Oct-2013[/TD]
[/TR]
[TR]
[TD="align: right"]09-Oct-2013[/TD]
[TD="align: right"]26-Oct-2013[/TD]
[TD="align: right"]26-Oct-2013[/TD]
[TD="align: right"]26-Oct-2013[/TD]
[TD="align: right"]10-Nov-2013[/TD]
[TD="align: right"]09-Nov-2013[/TD]
[TD="align: right"]13-Oct-2013[/TD]
[TD="align: right"]01-Oct-2013[/TD]
[TD="align: right"]03-Oct-2013[/TD]
[TD="align: right"]12-Oct-2013[/TD]
[/TR]
[TR]
[TD="align: right"]02-Nov-2013[/TD]
[TD="align: right"]30-Oct-2013[/TD]
[TD="align: right"]28-Oct-2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19-Oct-2013[/TD]
[TD="align: right"]09-Oct-2013[/TD]
[TD="align: right"]09-Oct-2013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]07-Nov-2013[/TD]
[TD="align: right"]07-Nov-2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]07-Nov-2013[/TD]
[TD="align: right"]14-Oct-2013[/TD]
[TD="align: right"]12-Oct-2013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29-Oct-2013[/TD]
[TD="align: right"]19-Oct-2013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]05-Nov-2013[/TD]
[TD="align: right"]23-Oct-2013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02-Nov-2013[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


@ Thanks, Aladin for helping, I dont have account with Skydrive, or could not make out other site, so, Here I've uploaded the file also - Revised Loop Query

Hope this helps u to get the basic, data. The sample data is just D3:M9, in reality, range possibly could be D3:V100