CODEWITHSUNDEEP
excelvba
chandini v
chandini v

Reputation: 11

Worksheet_Calculate() - Record dates when a cell's value is changed

I am a beginner in VBA.

I have a Column "AA" can have multiple values, driven by formula of which 2 are "Impact Assessed" or "Ready for retesting".

Problem Statement - I want to record the dates when cell's value is changed to Impact Assessed and Ready for Retesting in 2 separate columns - Column B and Column C, respectively. I also want the ability of the macro to run if I copy-paste more than 1 cells triggering the change in my Column AA.

Below is my code - 

Private Sub Worksheet_Calculate()

Dim rng As Range
  If Target.CountLarge > 1 Then Exit Sub
Set rng = Application.Intersect(Me.Range("AA:AA"), Target)

If Not rng Is Nothing Then
    Select Case (rng.Value)
        Case "2 - Impact Assessed": rng.Offset(0, 1).Value = Date
        Case "4 - Ready for retesting": rng.Offset(0, 2).Value = Date
    End Select
End If

End Sub

Upvotes: 0

Views: 94

Answers (1)

Tim Williams
Tim Williams

Reputation: 166895

Something like this:

Private Sub Worksheet_Calculate()

    Dim rng As Range, c As Range, v

    'get all cells with formulas
    On Error Resume Next
    Set rng = Me.Range("A:A").SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0

    'got some cells - loop over each cell
    If Not rng Is Nothing Then
        For Each c In rng.Cells
            'make sure there's no error
            If Not IsError(c.Value) Then
                Select Case c.Value
                    Case "2 - Impact Assessed"
                        AddDateIfEmpty c.Offset(0, 1)
                    Case "4 - Ready for retesting"
                        AddDateIfEmpty c.Offset(0, 2)
                End Select
            End If
        Next c
    End If
End Sub

'utility sub: add date only if not already present
Sub AddDateIfEmpty(c As Range)
    If Len(c.Value) = 0 Then c.Value = Date
End Sub

Upvotes: 1

Related Questions