Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

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

Posted on 2016-09-27
Medium Priority
75 Views
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.

0
Question by:Omar Hernandez
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 5

LVL 46

Expert Comment

ID: 41820409
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
0

Author Comment

ID: 41820427
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
``````

a code which will do it automatically pasted on the main page, without having it on all the cell
0

LVL 46

Expert Comment

ID: 41821532
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
``````
0

Author Comment

ID: 41821569
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.
0

Author Comment

ID: 41821585
File is on this attachment
BK-TO-DO-LIST.xlsm
0

LVL 46

Accepted Solution

aikimark earned 2000 total points
ID: 41821651
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim eval As Long
Dim colors As Variant
colors = Array(0, -16776961, 26367, 26367, 26367, -65536)
If Target.Column = 2 Then
If Cells(Target.Row, 2).Value = Empty Then
Cells(Target.Row, 10).Value = ""
Exit Sub
End If
If IsDate(Cells(Target.Row, 2).Value) Then
eval = Cells(Target.Row, 2).Value - Date
Select Case eval
Case 0
Cells(Target.Row, 10).Value = ""
Case 1 To 4
Cells(Target.Row, 10).Value = String(eval, "Q")
Cells(Target.Row, 10).Font.Color = colors(eval)
Case Is > 4
Cells(Target.Row, 10).Value = String(5, "Q")
Cells(Target.Row, 10).Font.Color = colors(5)
End Select
With Cells(Target.Row, 10).Font
.name = "Snap ITC"
.FontStyle = "Bold"
.Size = 8
End With
Cells(Target.Row, 9).Value = eval
End If
End If

End Sub
``````
0

Author Closing Comment

ID: 41821678
thanks a mil aikimark, works like a charm... :)
0

Author Comment

ID: 41821699
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 ?
0

LVL 46

Expert Comment

ID: 41821727
Possibly.  What do you mean by "rearrange"?
0

Author Comment

ID: 41821819
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
0

LVL 46

Expert Comment

ID: 41822050
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
###### Suggested Courses
Course of the Month9 days, 20 hours left to enroll