Solved

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

Posted on 2016-09-27
11
65 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
[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
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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
 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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…

752 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