-
Feb 2nd, 2021, 03:20 AM
#1
Thread Starter
New Member
-
Feb 2nd, 2021, 03:37 AM
#2
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.
-
Feb 2nd, 2021, 03:52 AM
#3
Thread Starter
New Member
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!
-
Feb 2nd, 2021, 04:01 AM
#4
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.
-
Feb 2nd, 2021, 05:11 AM
#5
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
-
Feb 2nd, 2021, 07:15 AM
#6
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
-
Feb 3rd, 2021, 02:49 AM
#7
Thread Starter
New Member
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!
-
Feb 3rd, 2021, 03:50 AM
#8
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
-
Feb 3rd, 2021, 08:29 AM
#9
Thread Starter
New Member
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...
-
Feb 3rd, 2021, 09:34 AM
#10
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.
-
Feb 3rd, 2021, 03:01 PM
#11
Re: Simple VB code for date comparison and formatting
i can open the attachment from the link above, but i will do it again
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
-
Feb 4th, 2021, 02:04 AM
#12
Thread Starter
New Member
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()
-
Feb 4th, 2021, 03:30 AM
#13
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
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
-
Feb 4th, 2021, 06:01 AM
#14
Thread Starter
New Member
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.
-
Feb 4th, 2021, 06:18 AM
#15
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
-
Feb 4th, 2021, 06:50 AM
#16
Thread Starter
New Member
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!
-
Feb 9th, 2021, 03:02 AM
#17
Thread Starter
New Member
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
-
Feb 9th, 2021, 04:35 AM
#18
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
-
Feb 9th, 2021, 06:43 AM
#19
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|