The following code is in the WorkSheet_Change function.
If Target.Column = 7 Then
Dim useThisDate As Date
Dim loopRange As Range
Dim r As Long
Dim numRows As Long
useThisDate = Range(Cells(Target.Row, 5), Cells(Target.Row, 5)).Value
'If useThisDate <> "" Then
DetermineUsedRange loopRange
numRows = loopRange.Rows.Count
For r = 7 To (numRows + 7) Step 1
If loopRange.Cells(r, 5).Value = useThisDate Then
loopRange.Cells(r, 7).Value = Target.Value
End If
Next r
'End If
End If
Here is the problem. Since I change the value in column 7 (loopRange.Cells(r,7).Value = Target.Value) in the For loop then the Worksheet_Change function gets invoked again.
How do I disable the Worksheet_Change function until the For loop finishes? If I change the 7 to an 8 (place the value one column to the right) then this thing works fine.
Also, how do I determine if useThisDate is a blank cell? I can't figure that out either.
Thanks.
Thomas
Bookmarks