Link to home
Start Free TrialLog in
Avatar of Omar Hernandez
Omar Hernandez

asked on

Date generator for it to show Days Left for due date.

Hello guys, i have an alternative to make this possible by adding this code on the cell =IF(B6<>"",B6-TODAY(),"")

***But what i truly want is have a way where a code is created, so that i may attach it in the main page and it will calculate automatic date left from searching column "B" and display it in column "I".

again thanks for your time guys and help.

User generated image
Avatar of aikimark
aikimark
Flag of United States of America image

If you place that formula into a cell and double-click the lower right corner of the cell, it will autofill down.  That should give you what you want
Avatar of Omar Hernandez
Omar Hernandez

ASKER

Hello there Aikimark, thanks for your response, i know but i am looking for another way of doing it. I have a code that i have pasted on the main page and i would like something similar like this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim eval As Long
If Target.Column = 2 Then
    eval = Cells(Target.Row, 2).Value - Date
    If eval > 0 Then
        If eval = 1 Then
            Cells(Target.Row, 10).Value = "Q"
            Cells(Target.Row, 10).Font.Color = -16776961
        ElseIf eval = 2 Then
            Cells(Target.Row, 10).Value = "QQ"
            Cells(Target.Row, 10).Font.Color = 26367
        ElseIf eval = 3 Then
            Cells(Target.Row, 10).Value = "QQQ"
            Cells(Target.Row, 10).Font.Color = 26367
        ElseIf eval = 4 Then
            Cells(Target.Row, 10).Value = "QQQQ"
            Cells(Target.Row, 10).Font.Color = 26367
        ElseIf eval > 4 Then
            Cells(Target.Row, 10).Value = "QQQQQ"
            Cells(Target.Row, 10).Font.Color = -65536
        End If
        Cells(Target.Row, 10).Font.name = "Snap ITC"
        Cells(Target.Row, 10).Font.FontStyle = "Bold"
        Cells(Target.Row, 10).Font.Size = 8
    Else
        Cells(Target.Row, 10).Value = ""
    End If
    If Cells(Target.Row, 2) = Empty Then
        Cells(Target.Row, 10).Value = ""
    End If
End If

End Sub

Open in new window


a code which will do it automatically pasted on the main page, without having it on all the cell
This would be better
Private Sub Worksheet_Change(ByVal Target As Range)
Dim eval As Long
If Target.Column = 2 Then
    If Cells(Target.Row, 2) = Empty Then
        Cells(Target.Row, 10).Value = ""
        Exit Sub
    End If
    eval = Cells(Target.Row, 2).Value - Date
    If eval > 0 Then
        If eval = 1 Then
            Cells(Target.Row, 10).Value = "Q"
            Cells(Target.Row, 10).Font.Color = -16776961
        ElseIf eval = 2 Then
            Cells(Target.Row, 10).Value = "QQ"
            Cells(Target.Row, 10).Font.Color = 26367
        ElseIf eval = 3 Then
            Cells(Target.Row, 10).Value = "QQQ"
            Cells(Target.Row, 10).Font.Color = 26367
        ElseIf eval = 4 Then
            Cells(Target.Row, 10).Value = "QQQQ"
            Cells(Target.Row, 10).Font.Color = 26367
        ElseIf eval > 4 Then
            Cells(Target.Row, 10).Value = "QQQQQ"
            Cells(Target.Row, 10).Font.Color = -65536
        End If
        Cells(Target.Row, 10).Font.name = "Snap ITC"
        Cells(Target.Row, 10).Font.FontStyle = "Bold"
        Cells(Target.Row, 10).Font.Size = 8
    Else
        Cells(Target.Row, 10).Value = ""
    End If
End If

End Sub

Open in new window

Thanks Akimark, for me to be more specific i want a code so that i may put on the main page( the same page where i have the one you have polished) where it calculate the date from "B" column and is input on the "I" column showing numbers.
***Those numbers = amount of days from now to the amount of days it will take, to get to the date it is display on column "B".
***(Example) on row, column "B" = 10/4/16 on column "I" it will = 5)
***(Example) on row, column "B" = 10/9/16 on column "I" it will = 10)
***(Example) on row, column "B" = 11/4/16 on column "I" it will =36 )

Hope this is clear friend.
File is on this attachment
BK-TO-DO-LIST.xlsm
ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks a mil aikimark, works like a charm... :)
Hey there aikimark, quick question is it possible to have this code to work in a way, that when i click on the refresh button the code will re-check all the dates on the "B" column and re-arrange dates left on the "I" column ?
Possibly.  What do you mean by "rearrange"?
Hey aikimark i have made a video for a better visual description if you may, please check it out on the link provided.

http://www.screencast.com/users/Omarantonio1231/folders/Refresh%20Dates/media/f19fa1e9-3805-40f9-8bb7-ced979e14608
I think it should be possible.  However, this is a closed question.  Please post a new question about this new requirement.