Try this worksheet event cde
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Const kFull As String = "Full Audit"
Dim startCell As Range
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("K4:S8")) Is Nothing Then Exit Sub
On Error GoTo ws_exit
Application.EnableEvents = False
With Target
If .Value = "Sense" Then
If Not IsError(Application.Match(kFull, Me.Rows(.Row), 0)) Then
Set startCell = Me.Cells(.Row, Application.Match(kFull, Me.Rows(.Row), 0))
Else
Set startCell = Me.Cells(.Row, "C").Offset(0, 1)
End If
If Application.CountIf(startCell.Resize(, .Column - startCell.Column + 1), .Value) = 9 Then
.Offset(0, 1).Value = kFull
End If
End If
End With
ws_exit:
Application.EnableEvents = True
End Sub
Bookmarks