Results 1 to 19 of 19

Thread: Simple VB code for date comparison and formatting

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2021
    Posts
    12

    Simple VB code for date comparison and formatting

    Hi all,

    I would like to do some VB coding where a relative simple date comparison leads to a formatting result. In the screenshot below there are 6 project represented on the A column. In the columns D up to K there will be processes with 'plannend' and 'actual' dates. I would like to work towards a VB code that:

    - 'actual' date will be formatted red when the 'actual' date overruns 'planned' - there can be multiple dates in one row (multiple processes)
    - 'actual' date will be formatted green when the 'actual' date is lower as 'planned' - there can be multiple dates in one row (multiple processes)
    - There is no action needed when 'n.a.' is entered

    I am relative new to coding, hence I was not able to provide a starting code. It will be most appreciated when you can help me out!


    Name:  Schermafbeelding 2021-02-02 091053.jpg
Views: 230
Size:  25.4 KB

    -Steffen92
    Attached Images Attached Images  

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Simple VB code for date comparison and formatting

    Is this an Excel add-in or is this something you want to do in a VB application? It looks like the former but you've made no mention of that at all. Either way you have omitted very relevant information.

  3. #3

    Thread Starter
    New Member
    Join Date
    Feb 2021
    Posts
    12

    Re: Simple VB code for date comparison and formatting

    Hi jmcilhinney,

    as is said, i am relative (very) new to VB and also this forum. I will be running this in the Excel add-in. Is there more information you need to know in order for you, or others, to help me out?

    Thanks in advance!

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Simple VB code for date comparison and formatting

    I'd say that's all that's required - it would be very different code if Excel was not involved - but I have asked the mods to move this to the Office Development forum, which is a more appropriate place for it.

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: Simple VB code for date comparison and formatting

    Welcome to VBForums

    Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum... note that while it certainly isn't made clear, the "VB Editor" in Office programs is actually VBA rather than another variation of VB

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Simple VB code for date comparison and formatting

    while this could easily be coded, it would probably be simpler and more appropriate to use excel's conditional formatting of ranges (cells), from the home menu

    if you wish to use code, you can put the code into the worksheet_change event so the code will run automatically when the cell value is changed
    something like
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("d3:k14")) Is Nothing Then
            If Target > Target.Offset((Target.Row Mod 2) - 1) Then
                Target.Offset((Target.Row Mod 2) * -1).Interior.Color = vbRed
                ElseIf Target < Target.Offset(((Target.Row Mod 2) * -1)) Then Target.Offset((Target.Row Mod 2) * -1).Interior.Color = vbGreen
                Else: Target.Offset((Target.Row Mod 2) * -1).Interior.Color = 16777215
            End If
    End If
    End Sub
    this code is not correct, but it is too late for me now so will have to wait till tomorrow for me to fix (unless someone else wan to improve on it) if you want to go that way, but you should look at the conditional formatting
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    New Member
    Join Date
    Feb 2021
    Posts
    12

    Re: Simple VB code for date comparison and formatting

    Hi westconn1,

    as this file will be used by multiple colleagues, i am afraid that the formatting will get lost by colleages copy and pasting new rows's. Besides i do not quite know how to format a cell based on the above value. I do know how to compare cells to each other using the 'use formula to determine which cells to format' function, but i am not quite sure how to make a rule in such way it works based on intermittend data.

    Help will be most appreciated!

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Simple VB code for date comparison and formatting

    but i am not quite sure how to make a rule in such way it works based on intermittend data.
    Attachment 180082

    you may need an additional criteria to remove color for any cell that has n/a, nil or equal values
    the conditional formatting should apply to all cells in second row of each project
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9

    Thread Starter
    New Member
    Join Date
    Feb 2021
    Posts
    12

    Re: Simple VB code for date comparison and formatting

    Hi westconn1,

    did you attach something in your previous post? I am seeing 'Attachement 180082' but was not able to open the file...

  10. #10
    Administrator Steve R Jones's Avatar
    Join Date
    Apr 2012
    Location
    Largo, FL.
    Posts
    1,820

    Re: Simple VB code for date comparison and formatting

    Invalid Attachment specified. If you followed a valid link, please notify the administrator

    Westconn1 needs to click the GO Advanced button and add the image there.
    Wi-fi went down for five minutes, so I had to talk to my family....They seem like nice people.

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Simple VB code for date comparison and formatting

    i can open the attachment from the link above, but i will do it againName:  clipic.jpg
Views: 196
Size:  24.1 KB
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

    Thread Starter
    New Member
    Join Date
    Feb 2021
    Posts
    12

    Re: Simple VB code for date comparison and formatting

    Hi Westconn1,

    Thanks for your reply. I am not quite sure that this will allow me to work with intermitted data (even and un-eve rows).

    The following functions came to my attention. Do you know how to modify these into the formatting area in order to provide the formatting i need?

    =MOD(A2, 2) or
    =ISEVEN() or =ISODD()

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Simple VB code for date comparison and formatting

    I am not quite sure that this will allow me to work with intermitted data (even and un-eve rows).
    as you only want to colour rows in the second row for each project, you only put the conditional formatting for those even rows, editing either row will change the colour if the comparative values change
    conditional formatting is formula based
    you could use vba to actually assign the conditional formatting to all even row cells in the required range



    =MOD(A2, 2)
    i could not for the life of me remember how to convert the mod from the row to the required offset, even though i had done it in the past
    in vba the mod function is slightly different

    target.row mod 2 will return 0 or 1, 0 for even rows
    so for even rows you need to compare with the row above, target.offset(-1), odd rows you need to compare with target.offset(1)
    the calculation should have been
    target.offset((target.row mod 2) *2 -1)
    for even row that would be 0 * 2 -1 = -1
    and odd rows 1 *2 -1 = 1

    from that you can do all the comparisons without some criteria of if even row else if odd row
    but it got to be 11pm when i was putting the sample together and late enough for me not to fix at that time
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  14. #14

    Thread Starter
    New Member
    Join Date
    Feb 2021
    Posts
    12

    Re: Simple VB code for date comparison and formatting

    Hi Westconn1,

    I want to format the cells in advance so that the formatting rules apply even after adding a new row. To get a better understanding on my challenge: this file will be used by multiple colleagues so i aspire to make it 'idiot proof'. I don't have any trouble by formatting 1 individual row. The challenge really is to program it in such matter that every even-row has the formatting rule pre-set.

    Would be great to hear from you once more.

  15. #15
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Simple VB code for date comparison and formatting

    you could apply conditional formatting to every even row in the worksheet, or the same could apply to changing the cell colours by code, the initial sample limited to the cells within a specific range, but it does not need to do so
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  16. #16

    Thread Starter
    New Member
    Join Date
    Feb 2021
    Posts
    12

    Re: Simple VB code for date comparison and formatting

    Hi Westconn1,

    would you be able to show me how it's done by sharing some printscreens? Thanks!

  17. #17

    Thread Starter
    New Member
    Join Date
    Feb 2021
    Posts
    12

    Re: Simple VB code for date comparison and formatting

    Hi all,

    felt like sharing the solution. The formula which i have used in the conditional formatting is: =AND(IF(($Z7>$Z6);TRUE;FALSE);IF(ISEVEN(ROW());TRUE;FALSE);IF(ISBLANK($Z6);FALSE;TRUE))

    Attachment 180143

  18. #18
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Simple VB code for date comparison and formatting

    thank you for sharing, but
    invalid attachment
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  19. #19

    Thread Starter
    New Member
    Join Date
    Feb 2021
    Posts
    12

    Re: Simple VB code for date comparison and formatting

    Trying once more:

    Attachment 180145

Posting Permissions

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



Click Here to Expand Forum to Full Width