Compare Each Cell in Columns (with some alternates) and format cell, if condition is met (True)

[ANALYSTBANK]

My current data sheet looks as under;

Row	ColumnK	ColumnL	ColumnM	ColumnN	ColumnO
3					21-Sep-2013
4					27-Sep-2013
5					03-Oct-2013
6					11-Oct-2013
7					23-Oct-2013
8				17-Sep-2013
9				01-Oct-2013
10				08-Oct-2013
11				23-Oct-2013
12			29-Sep-2013
13			11-Oct-2013
14			13-Oct-2013
15			20-Oct-2013
16		24-Sep-2013
17		21-Oct-2013
18	21-Sep-2013
19	23-Sep-2013
20	02-Oct-2013
21	13-Oct-2013
22	22-Oct-2013
23	26-Oct-2013

<tbody>
</tbody>

I want 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 by COLOR 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 is between Column K and Column L, and that is where I need your help.

I will repeat the process later for comparison between Column L and Column M, and then Column M, and N and so on, BUT FIRST COLUMN COMPARISON between ColumnK, and ColumnL, I need help.

How to do it?

Thanks

 

[ANALYSTBANK]

For question #10 above, got the solution, i am now using

If Cells(x, l).Value = vbNullString Then Cells(x, l).Delete Shift:=xlUp

 

[ANALYSTBANK]

My current data sheet looks as under;

Row	ColumnK (Col Index 11)	ColumnL (Col Index 12)	ColumnM (Col Index 13)	ColumnN (Col Index 14)	ColumnO (Col Index 15)
2	Header	Header	Header	Header	Header
3					21-Sep-2013
4					27-Sep-2013
5					03-Oct-2013
6					11-Oct-2013
7					23-Oct-2013
8				17-Sep-2013
9				01-Oct-2013
10				08-Oct-2013
11				23-Oct-2013
12			29-Sep-2013
13			11-Oct-2013
14			13-Oct-2013
15			20-Oct-2013
16		24-Sep-2013
17		21-Oct-2013
18	21-Sep-2013
19	23-Sep-2013
20	02-Oct-2013
21	13-Oct-2013
22	22-Oct-2013
23	26-Oct-2013

<tbody>
</tbody>

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