Hello everyone,
I'm still learning VBA and currently am very limited with vlookups to what I need to do in my daily tasks.
I'm reading through a VBA book but I simply can't understand how to put it all together.
I'll explain what I'm trying to do and what I think I should used, but I need help in writing the code.
I'm collecting data on suppliers who change their delivery dates constantly so I have columns labelled from G to AG as Rev 0 to Rev 26 where Rev 0 is the first date they gave me and then every time they change the date to a new date, I simply move to the next column to the right and add the new date. Therefore I keep and history of the dates while tracking every date change. Each row is a different deliverable with a long list(columns) of revised dates.
I want excel to find the last active Rev # for each row and compare it to the date in Rev 0. The comparison should be a subtraction to find the difference between the two dates. Then I want this "difference" to be pasted in Column F.
--------------------------
My research so far:
Need to find last active cell in the row
Do Until IsEmpty(ActiveCell.Value)
ActiveCell.Offset(1,0).Select
Loop
- This should find the last active cell and select it. I now need to save this value into something...
- Then I need to select the date value in column G(Rev 0)
- Then I need to send them both into a function to compute the difference between both.
Then use a function to compute difference
Function ComputeDifference(Rev0,RevX)
Dim R0, RX As Integer
Result = RX-R0
End Function
Then I need to paste the value of the difference in column F
Range("F2").Activate
Paste.Value?
Now I need to move to the row below and repeat this process again....and again until the end of the sheet.
Lastly, I need to color the positive value as RED and the negative values as Green, I believe an If/else statement could work?
If Cell.value<0 Then
ActiveCell.Interior.Color = vbRed
If Cell.Value >0 Then
ActiveCell.Interior.Color = vbGreen
Else
ActiveCell.Interior.Color = vbWhite
Can anyone help me out?
Bookmarks