My current data sheet looks as under; (Sample File is attached)
[TABLE="width: 505"]
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
[/TABLE]
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.