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

vargur

New Member
Joined
Feb 29, 2016
Messages
5
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Fishboy

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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:

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

New Member
Joined
Feb 29, 2016
Messages
5
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:

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

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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:
Code:
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

New Member
Joined
Feb 29, 2016
Messages
5

ADVERTISEMENT

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

Try this:
Code:
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

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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

New Member
Joined
Feb 29, 2016
Messages
5

ADVERTISEMENT

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

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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:

Rich (BB code):
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,511
Messages
5,602,081
Members
414,500
Latest member
kevdragon1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top