Multiple If Statement actions

[davonovic]

Hi, I'm trying to get more than one cell to display a text string but it ends up getting caught into an infinite loop i think. I don't know how else to write this. Any suggestions/alternate methods are greatly appreciated.

Private Sub worksheet_change(ByVal target As Range)
Dim cell As Range

For Each cell In Range
With cell
If Cells(.Row, "C") > 0 Then
Cells(.Row, "B") = Int(Now)
Cells(.Row, "A") = "Completed"
Else
Cells(.Row, "B") = Null
End If
End With
Next cell
End Sub

 

[doofusboy]

I see several problems here. First, you never define what "Range" should be checked. You must tell the macro what range of cells you want to check (see Set statement below). Next, every time you change a cell inside the If statement, Excel will launch the Worksheet_Change event, because the worksheet is changing (makes sense right?). Disabling and enabling events will solve that problem. Lastly, learn to indent your code (makes debugging problems easier). The following code should do what you need.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim MyRange As Range
Set MyRange = Range("C1:C10")
For Each cell In MyRange
With cell
If Cells(.Row, "C") > 0 Then
Application.EnableEvents = False
Cells(.Row, "B") = Int(Now)
Cells(.Row, "A") = "Completed"
Application.EnableEvents = True
Else
Application.EnableEvents = False
Cells(.Row, "B") = Null
Application.EnableEvents = True
End If
End With
Next cell
End Sub