Excel / VBA: automatize tasks follow-up file

Hello experts,

I have the following file attached that I use to track my daily task.
2015-09-28-22_59_39-Microsoft-Excel-.pngI would like to automatize the file by adding some requirements through conditional formatting, data validation, vba etc..:

1-Once I enter an action (Column A, status should be automatically equal to Pending, I shouldn’t copy the row above but the information should be displayed automatically.
2-The same for Priority (Column B) which will be reported with 1 by default but can be manually modified if necessary.
3-If I enter a task, the Schedule Day (Column C) should be automatically displayed with the date when It was enter the task (ex: if I enter a task today the Schedule day should be equal to today).
 I shouldn’t copy the above line it should be automatically displayed.
4-Once I enter 100% in completed (Column F) the effective date should be equal to the corresponding date when the 100% was entered and the status should be equal to Done:

Ex: I enter in line 2 task 1 then automatically:
The status should be equal to pending, the Schedule day should be equal to 28/09/15 or 09/28/15, priority equal to 1 Effective Day equal to blank and Completed equal to 0% the color conditional formatting should be kept.
Tomorrow I report in F1 100 then the Effective day should be equal to 29/09/15 or 09/29/15 the Status should be equal to Done. In the meantime I report Task 2  in line 3the status should be equal to pending the Schedule day should be equal to 29/09/15 or 09/29/15, priority equal to 1 Effective Day equal to blank and Completed equal to 0%.

What I want to avoid is to manually copy the formula from the above line I would like to apply this logic for all the lines once I have enter tasks in Column A.

If you have questions, please don't hesitate to contact me.

Thank you very much for your help.
Tasks.xlsx
LVL 1
LD16Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
in the sheet1's module, try add and customize this..

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Err
    'Update Action
    If Target.Row > 1 And Target.Column = 1 Then
        If Target.Value <> "" Then
            If Cells(Target.Row, 2) = "" Then
                Cells(Target.Row, 2) = "Pending"
            End If
            If Cells(Target.Row, 3) = "" Then
                Cells(Target.Row, 3) = "1"
            End If
            If Cells(Target.Row, 4) = "" Then
                Cells(Target.Row, 4) = Date
            End If
            If Cells(Target.Row, 6) = "" Then
                Cells(Target.Row, 6) = 0
            End If
        End If
    End If
    'Update Completed
    If Target.Row > 1 And Target.Column = 6 Then
        If Cells(Target.Row, 6) = 1 Then
            Cells(Target.Row, 5) = Date
            Cells(Target.Row, 2) = "Done"
        End If
    End If
Err:
End Sub

Open in new window

Tasks_b.xlsm
LD16Author Commented:
Working perfectly.

Just two little remarks:

1-For the Conditional formatting is there a way to apply a dynamic range in order to automatically apply conditional formating for the actions already filled in Column A

The current version apply conditional formating for a fix Range:



I would like to apply a dynamic range as long as column A is already filled:

2-Add in the private sub borders from A to F as long as Action A is already filled:

Ex: I entered information in 153 I should have the borders from A153 and F153 and also based on the Column F the conditional formating of Completed.

2015-09-29-08_20_51-Microsoft-Excel-.png

Thank you very much for your help.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>1-For the Conditional formatting is there a way to apply a dynamic range in order to automatically apply conditional formating for the actions already filled in Column A

perhaps you can try applied range:

=$F:$F

Conditional Formatting setting
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>2-Add in the private sub borders from A to F as long as Action A is already filled:

you can add this codes to create the borders for current row:

'Draw borders
    If Target.Row > 1 Then
        Set r = Range("A" & Target.Row & ":E" & Target.Row)
        r.Borders(xlDiagonalDown).LineStyle = xlNone
        r.Borders(xlDiagonalUp).LineStyle = xlNone
        With r.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With r.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With r.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With r.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With r.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With r.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
    End If

Open in new window

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
so, final sample as attached.
Tasks_c.xlsm
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Just a side note... remember to format all the cells in Column F: Completed as "Percentage".
LD16Author Commented:
Thank you very much for this, I fogot the last requirement, do you know if there is a way in VBA or excel to force the file save every 5 minutes without a popup message and right after I close the file.

Thank you very much for your help.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>do you know if there is a way in VBA or excel to force the file save every 5 minutes without a popup message and right after I close the file.
Perhaps you can have a read on this article.

Description of the AutoRecover functions in Excel
https://support.microsoft.com/en-us/kb/289273

but personally won't suggest to use scripts to auto-save the file as you probably won't manage to recover your file if you accidentally close it. That's a risk of data lost.
LD16Author Commented:
Thank you very much for your help.

I am have added the private sub for the automatic save every  minute and I don't why I got an error message when I don't launch the macro after 1 minute.

Cannot run the macro "macroname". The macro may not be available in this workbook or all macros may be disabled

However if I launch the macro manually I am able to calling properly.

2015-09-29-11_21_05-Microsoft-Excel-.png
Thank you very much for your help.
Daily_Tasks_2.xlsm
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
in your Workbook module, add:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.Save
End Sub

Private Sub Workbook_Open()
    Call AutoSaveAs
End Sub

Open in new window

then create a new public module file, and add:
Public Sub AutoSaveAs()
    Application.OnTime Now + TimeValue("00:01:00"), "SaveFile"
End Sub

Public Sub SaveFile()
    ThisWorkbook.Save
    Application.OnTime Now + TimeValue("00:01:00"), "SaveFile"
End Sub

Open in new window

Daily_Tasks_2_b.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
btw, I have removed that msgbox, you may add it back if necessary.
LD16Author Commented:
Perfect, working perfectly.

Thank you again for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.