Compare each cell in Range with each other with 6 alternates, and highlight

  • My current data sheet looks as under; (Sample File is attached)

    [TABLE="width: 505"]

    [tr]


    [td]

    Row

    [/td]


    [td]

    ColumnK

    [/td]


    [td]

    ColumnL

    [/td]


    [td]

    ColumnM

    [/td]


    [td]

    ColumnN

    [/td]


    [td]

    ColumnO

    [/td]


    [/tr]


    [tr]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/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]


    [/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.

  • Re: Compare each cell in Range with each other with 6 alternates, and highlight


    cytop, Yes, from him only, very precise solution and speedy execution pf code.


    Thanks for looking in here. I'll take care not to post in 2-3 forums.


    I'll take care of the rules, with due respect. If offended, apology.


    I started this thread on Aug 21, and after 23 DAYS, i asked for help there, and i got reply very next day there.


    I know the seniors r too busy, and this forum is also resource rich.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!