Count until condition is met and then retain final count

[Fats]

Appreciate if some sharp individual can assist me with the following issue.

In cell A1 I have a date.
In cell B1 I have a number between 1 and 100.

I need a formula in C1 to count the number of days between today and the
date in A1 until the number in A2 = 100.
However, Once A2 = 100 I need the formula in C1 to retain the final number
of days counted.

 

[Jacob Skaria]

You will have to use a VBA solution to acheive this. Select the sheet tab
which you want to work with. Right click the sheet tab and click on 'View
Code'. This will launch VBE. Paste the below code to the right blank portion.
Get back to to workbook and try out.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Range("A2").Value = 100 Then
Range("C1") = Date - Range("A1")
Else
Range("C1").Formula = "=TODAY()-A1"
End If
Application.EnableEvents = True
End Sub

 

[Fats]

Hi Jacob,

The macro is doing exactly what I requested. Nice one.

Excuse my lack of VB knowledge but I am trying to enlargen the range of this
macro but am struggling to make it work. See below. Appreciate your steer on
this.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Range("E3:E10").Value = 100 Then
Range("G3:G10") = Date - Range("D310")
Else
Range("G3:G10").Formula = "=TODAY() - Range("D310")"
End If
Application.EnableEvents = True
End Sub