Clear cells on adjacent cell change

[Adam1979]

New to VBA but feel that using it is the solution to this question:

I have text in column F (cells 20 to 199) that changes automatically based on pre-determined time constraints (this is done via an IF formula). I want the 4 cells adjacent to F to clear contents when the text in F changes. For example when cell F126 changes text automatically, G126, H126, I126 and J126 all clear. If cell F99 changes, cells G99, H99, I99 and J99 clear. And so on.

I think that VBA is the answer but am not sure how to make it so.

 

[Adam1979]

Thanks Smitty but again, I am an absolute VBA virgin so am not sure what "set it to true at the end might" mean, would it look like this?

Application.EnableEvents = False
Private Sub Worksheet_Calculate()
Dim c As Range
For Each c In Range("F20:F199")
If c.Value <> 0 Then
Range(Cells(c.Row, "G"), Cells(c.Row, "J")).ClearContents
End If
Next c
True
End Sub


And do I go in to the worksheets VBA, delete what's already in there:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub


Thanks, Adam

 

[Smitty]

Application.EnableEvents needs to go in the procedure itself, not outside. And you need to add the Application call to the true part as well:

Private Sub Worksheet_Calculate()
  Dim c As Range
    Application.EnableEvents = False
      For Each c In Range("F20:F199")
        If c.Value <> 0 Then
          Range(Cells(c.Row, "G"), Cells(c.Row, "J")).ClearContents
        End If
      Next c
    Application.EnableEvents = True
End Sub

And you can delete the Change event if it's empty.

 

[Adam1979]

Ahh, this worked better but I tested it by deleting the contents of F20 though it deleted the contents of G20 all the way down to G199, H20 all the way down to H199 and J20 all the way down to J199. I only need it for example to delete G20, H20 and J20 when F20 is cleared. Would this code need a tweak to do that GHJ row adjacent the the F cleared?

Thanks again, I feel this is getting closer.

 

[Smitty]

You might want to change your test criteria:

If LenB(c.Value) = 0 Then