Solved

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

Posted on 2016-09-27
11
47 Views
Last Modified: 2016-09-29
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.

Date-Generator.png
0
Comment
Question by:Omar Hernandez
  • 6
  • 5
11 Comments
 
LVL 45

Expert Comment

by:aikimark
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

by:Omar Hernandez
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

Open in new window


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

Expert Comment

by:aikimark
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

Open in new window

0
 

Author Comment

by:Omar Hernandez
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

by:Omar Hernandez
ID: 41821585
File is on this attachment
BK-TO-DO-LIST.xlsm
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 41821651
Please test this
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

Open in new window

0
 

Author Closing Comment

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

Author Comment

by:Omar Hernandez
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 45

Expert Comment

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

Author Comment

by:Omar Hernandez
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 45

Expert Comment

by:aikimark
ID: 41822050
I think it should be possible.  However, this is a closed question.  Please post a new question about this new requirement.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now