Results 1 to 5 of 5

Thread: Compare Date from a Column Cells in alternate Columns Cells

  1. #1
    Member
    Join Date
    Aug 2013
    Posts
    59
    Rep Power
    11

    Compare Date from a Column Cells in alternate Columns Cells

    I have a worksheet wherein I have only 'DATE' value in range of cells, say, A2:V80

    Each column has about say 100 rows of data (or instead let the excel-vba code find the used row data, so that each row is considered)

    Now, I want a Macro that could

    1) Read a Date value from a cell from a Column, say, Column C, Say from Cell 'C2', and put it in an array

    2) From the Date in cell 'C2', variable should be made like, 4 days forward, and 4 days backward, so in all I would have 9 variables for cell single c2, like, independant value of cell C2 itself (Variable 1), and 4 days forward (4 Variables), and 4days backward (4variables)

    3) Now keeping in mind the value of these 9 variable from cell C2, code should find a similar date (value) (EXACT MATCH), not beyond 10 days from now, rather say, exact match falling due within NEXT 10 DAYS from now, from cell values in each row in alternate column i.e 'Column E' (skiping immediate 'column D'), and then from repeate this process in 'Column G', and so on until column used range i.e. upto 'Column V' in our case

    4) Wherever the hit is found, macro should note the date, and its cell location

    5) Now this process should go on for each row in 'ColumnC', i.e. it should repeate this procedure for Cell Date value in 'C3',C4,C5, and so on till last used row in Column C, say 'C100' in our case

    6) Once this is done with each in 'Column C', same thing should continue with each cell value in 'ColumnD', but it should now compare with data starting from 'Column F', and once done, compare each cell value in Column D, with 'Column H' and so on, untill column data range is over i.e. Column V, in our case

    7) AFter this entire loop is over, a Message Box should flash the result with some space i.e. Date, and its cell location, and also highlight the cells in active sheet


    ================================================== ================================================== =========

    I have a sheet wherein I have only DATE value in range of cells, say, A2:V80

    Each column have about say 100 rows of data, again only DATES

    Now, I want a Macro that could

    find 'identical dates' that is within the range of say, next 10 days from now, and highlight these identical dates (color the cell), and also flash it in message box with its cell location, like 12/10/2013 in cell M32 or so

    ================================================== ================================================== =========

  2. #2
    Junior Member
    Join Date
    Aug 2013
    Posts
    9
    Rep Power
    0
    This sounds a little over complicated. Could this be done with simple conditional formatting instead to highlight the cells you want?

  3. #3
    Member
    Join Date
    Aug 2013
    Posts
    59
    Rep Power
    11
    @Dexter

    You are right, and that is what I exactly do currently. But it is very cumbersome, and laborious,job that i need to do, repetitively, and hence trying to make a code for it.

  4. #4
    Junior Member
    Join Date
    Aug 2013
    Posts
    9
    Rep Power
    0
    Ah right, sorry hadn't taken in to account that you might want to do this across workbooks.

    Perhaps you could record a macro for the conditional formatting and tweak it to your liking? (e.g. to work for different ranges, remove horrible ".Select" statements, etc)
    Last edited by Dexter1759; 08-19-2013 at 03:18 PM. Reason: Added examples.

  5. #5
    Member
    Join Date
    Aug 2013
    Posts
    59
    Rep Power
    11
    Quote Originally Posted by Dexter1759 View Post
    Ah right, sorry hadn't taken in to account that you might want to do this across workbooks.

    Perhaps you could record a macro for the conditional formatting and tweak it to your liking? (e.g. to work for different ranges, remove horrible ".Select" statements, etc)
    Yes, what u say is right, and instead of getting result in msg box, conditional formatting thru vba code wud be nice. I'll modify the query, and also experimenting with, what i know with limited understanding.

Similar Threads

  1. Compare Date Columns In Excel And Pull The Latest Date
    By marentette010 in forum Excel Help
    Replies: 1
    Last Post: 07-31-2013, 11:56 PM
  2. Replies: 3
    Last Post: 05-23-2013, 11:17 PM
  3. automatic Add date in cells
    By Ryan_Bernal in forum Excel Help
    Replies: 1
    Last Post: 01-23-2013, 02:50 PM
  4. Replies: 2
    Last Post: 09-24-2012, 11:19 PM
  5. Count no of cells containing date
    By princ_wns in forum Excel Help
    Replies: 5
    Last Post: 04-16-2012, 10:37 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •