+ Reply to Thread
Results 1 to 12 of 12

Compare list and highlight

  1. #1
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,379

    Compare list and highlight

    Hello:

    Please refer to attached file.
    I need either VB Code or conditional formatting WHICH I PREFER to perform following task.
    This is to reconcile bank Statements.
    Column I thru K shows each day sales for that day which got depostid in the bank.
    The date in column I is the date of the sales and it is not the deposit date.

    Column F shows the date of deposit in the bank for the amount shown in column D.

    I need to make sure that the Sales recorded in column K are deposited in the bank.
    To visually see this, i have come across following logic.

    I have added 5 days grace period in column M to make the deposit for a day.
    So i am now assuming that owner as me will make the sales deposit for 1/1/2016 by 1/6/2016, 1/2/2016 by 1/7/2016 and so on.
    Now I would need VB Code or CF to comapre value in column K between dates in column I and M in Column D for date in column F
    Once amount is found then highlight the value in column D and Column K with GREEN.
    Example :

    Lets look at K2 = 410 between 1/1/2016 and 1/6/2016.
    Look up 410 between 1/1/2016 and 1/6/2016 in column D.
    We see 410 on 1/4/2016 in cell D2 so highlight D2 and K2

    Lets look at K3 = 490 between 1/2/2016 and 1/7/2016.
    Look up 490 between 1/2/2016 and 1/7/2016 in column D.
    We see 490 on 1/4/2016 in cell D4, so highlight D4 and K3

    and so on....

    Let me know if you have any questions.
    Thanks.

    Riz
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,661

    Re: Compare list and highlight

    Is it not possible to have duplicate?:
    Same amount with an overlapped time frame
    - Battle without fear gives no glory - Just try

  3. #3
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,379

    Re: Compare list and highlight

    Hi PCI :

    Yes, pick up 1st one on the list.

    Riz

  4. #4
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,661

    Re: Compare list and highlight

    See next code
    Check and double check

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by PCI; 05-24-2016 at 04:21 PM.

  5. #5
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,379

    Re: Compare list and highlight

    Hello PCI:

    GREAT WORKKKKKKKKKKKKKKKK..
    It seems to do what I have explained.
    I will keep on adding monthly Bank info and Sales deposit info so this data will keep on growing for whole year.
    I would like to add code so that at the beginning the highlighted part is removed for fresh start for full data.
    I would try and test further and let you know if any issues or if any feature needs to be added.
    Thanks a lot \
    Riz

  6. #6
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,661

    Re: Compare list and highlight

    "add code so that at the beginning the highlighted part is removed for fresh start for full data"
    See next code

    Please Login or Register  to view this content.

  7. #7
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,379

    Re: Compare list and highlight

    Hi PCI:

    Thanks a bunch.
    If it is not too much of trouble would it be possible to add RED highlight in column D Unhighlighted cell which needs to be done at the end of the code. (The Cells which does not reconcile)

    Please let me know if you have any questions.
    Thanks.

    Riz

  8. #8
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,661

    Re: Compare list and highlight

    See here

    Please Login or Register  to view this content.

  9. #9
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,379

    Re: Compare list and highlight

    Hello PCI:

    Please refer to attached file.

    This works great.
    However, i have to scroll up and down with large data which does not concile.
    I am thinking that below could reduce this issue.
    I will create a seperate sheet "UnReconcile"
    After running your current code, the Red highlighted data can be pasted in "Unreconcile" Sheet (Column D,F and G) and Column I,J,K,M)
    I have manually pasted some.

    Let me know if you have any questions.
    Thanks.
    Riz
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,661

    Re: Compare list and highlight

    I can understand that you may need another treatment concerning the display of values found:
    on the same row the same "Amount" and "Cash Dep"
    but for value not found why not to filter by Red color ?

  11. #11
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,379

    Re: Compare list and highlight

    Hi PCI:

    Yes we can do that but remember there are 2 sets of data.
    One from Bank Statement and one from me entered.
    Please let me know if you have any questions.
    Thanks.

    Riz

  12. #12
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,661

    Re: Compare list and highlight

    See file attached with a filter for red values: What makes a difference with the sample you sent ...?
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Compare a List and Highlight Same Text
    By knuckledownlacrosse in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-12-2014, 10:15 PM
  2. Compare a List and Highlight Similarities
    By knuckledownlacrosse in forum Excel General
    Replies: 3
    Last Post: 02-25-2014, 09:53 AM
  3. [SOLVED] Compare name list (First name, last name) on two different sheets and highlight
    By badboynads in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-25-2013, 07:37 AM
  4. [SOLVED] Compare and Highlight
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-10-2012, 10:45 AM
  5. compare list and highlight
    By Blake 7 in forum Excel General
    Replies: 6
    Last Post: 11-10-2010, 06:44 AM
  6. How do you compare 2 list of numbers and highlight the difference
    By Jay Jones in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-20-2006, 12:50 PM
  7. RE: How do you compare 2 list of numbers and highlight the difference
    By Tom Ogilvy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-20-2006, 10:55 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1