VBA for stop Counting Days but retain the value as another cell is populated

[vargur]

Hi guys,
Could any one please help me write a vba for counting days from a start date entered in column A. Column B is formula'=TODAY()-B' . Column C is a list (Pending,Documentation,Completed) . What I need is column B should stop counting but retain the number as column C is populated as 'Completed'.

It would be appreciated column B changes colour to green

Thanking you in advance

 

[Fishboy]

Hi guys,
Could any one please help me write a vba for counting days from a start date entered in column A. Column B is formula'=TODAY()-B' . Column C is a list (Pending,Documentation,Completed) . What I need is column B should stop counting but retain the number as column C is populated as 'Completed'.

It would be appreciated column B changes colour to green

Thanking you in advance

Hi vargur, welcome to the boards.

If I am understanding you correctly then i think this will do the job.

First make a COPY of your workbook to test in. Next right-click on the tab name of your sheet and select View Code. In the new window that opens simply copy and paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C:C")) Is Nothing Then
        If Target.Value = "Completed" Then
            Range("B" & Target.Row).Value = Range("B" & Target.Row).Value
        End If
    End If
End Sub

Basically if any cell in column C is updated with a value of Completed then the corresponding cell in column B of that row is updated with the raw data value instead of the formula that was producing it.

 

[vargur]

Hi vargur, welcome to the boards.

If I am understanding you correctly then i think this will do the job.

First make a COPY of your workbook to test in. Next right-click on the tab name of your sheet and select View Code. In the new window that opens simply copy and paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C:C")) Is Nothing Then
        If Target.Value = "Completed" Then
            Range("B" & Target.Row).Value = Range("B" & Target.Row).Value
        End If
    End If
End Sub

Basically if any cell in column C is updated with a value of Completed then the corresponding cell in column B of that row is updated with the raw data value instead of the formula that was producing it.

That was super quick and well working. Highly appreciated.
Is it possible to include the 2nd part of my query (change of cell colour to green) incorporated in this code please

 

[Fishboy]

That was super quick and well working. Highly appreciated.
Is it possible to include the 2nd part of my query (change of cell colour to green) incorporated in this code please

Heh, sorry I totally didn't read that last line the first time around it seems!

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C:C")) Is Nothing Then
        If Target.Value = "Completed" Then
            Range("B" & Target.Row).Value = Range("B" & Target.Row).Value
                Range("B" & Target.Row).Interior.ColorIndex = 4
        End If
    End If
End Sub

Anyway, I hope this helps further. Thanks for the positive feedback

 

[vargur]

Heh, sorry I totally didn't read that last line the first time around it seems!

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C:C")) Is Nothing Then
        If Target.Value = "Completed" Then
            Range("B" & Target.Row).Value = Range("B" & Target.Row).Value
                Range("B" & Target.Row).Interior.ColorIndex = 4
        End If
    End If
End Sub

Anyway, I hope this helps further. Thanks for the positive feedback

Worked perfectly. Thanks a million Fish Boy.

You correctly understood my requirement in the first place even though part of my query was incorrectly typed. For any one looking for such a solution, for them to understand the scenario, my formula in column B was '=TODAY()-A' - not '=TODAY()-B'

Once again thanking mrexcel and the super members

 

[Fishboy]

Worked perfectly. Thanks a million Fish Boy.

You correctly understood my requirement in the first place even though part of my query was incorrectly typed. For any one looking for such a solution, for them to understand the scenario, my formula in column B was '=TODAY()-A' - not '=TODAY()-B'

Once again thanking mrexcel and the super members

Fortunately the end result was not affected by the typo. Whatever the result of the formula was in column B was simply turned from formula to raw result, the formula itself was irrelevant.

Thanks once again for the positive feedback. If you have any other issues or questions don't forget to drop by again!

 

[vargur]

Fortunately the end result was not affected by the typo. Whatever the result of the formula was in column B was simply turned from formula to raw result, the formula itself was irrelevant.

Thanks once again for the positive feedback. If you have any other issues or questions don't forget to drop by again!

Thanks for your encouragement,

If I need to add another variable in the list eg.'N/A' which too should stop the count, could you help it please

 

[Fishboy]

Thanks for your encouragement,

If I need to add another variable in the list eg.'N/A' which too should stop the count, could you help it please

Probably something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C:C")) Is Nothing Then
        If Target.Value = "Completed" Or Target.Value = "N/A" Then
            Range("B" & Target.Row).Value = Range("B" & Target.Row).Value
                Range("B" & Target.Row).Interior.ColorIndex = 4
        End If
    End If
End Sub

 

[vargur]

Thanks once again Fish Boy. Have a Good Evening

 

[Fishboy]

Thanks once again Fish Boy. Have a Good Evening

You're welcome