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.

 

[Smitty]

Welcome to the Board!

You could possibly use the Sheet_Calculate event, but a few questions first:

How are the adjacent cell values being populated/changing now? Are there cases where you wouldn't want to clear them? What if the value in F evaluates to the same value as previously? What's driving the changes in column F?

 

[Adam1979]

Welcome to the Board!

You could possibly use the Sheet_Calculate event, but a few questions first:

How are the adjacent cell values being populated/changing now? Are there cases where you wouldn't want to clear them? What if the value in F evaluates to the same value as previously? What's driving the changes in column F?

The changes in F are statements that change through time. It's about getting in touch with customers for after sales service. Different instructions appear after a month, a year, 18 months, 2 years and three years via the if formula, driven by Today(). The instruction text will never repeat, it's a different statement each time. The three cells adjacent are manually edited; G is a sales persons name selected from a drop down list, H is the date said action was taken, I is a hidden cell (so is useless and can actually be ommitted from this query) and J is a miscillaneous box to type a note in if needed. All G,H, (I), J need to be cleared when F changes, showing a new instruction.

 

[Smitty]

See if this does what you need:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range("F20:F199")<br>            <SPAN style="color:#00007F">If</SPAN> c.Value <> 0 <SPAN style="color:#00007F">Then</SPAN><br>                Range(Cells(c.Row, "G"), Cells(c.Row, "J")).ClearContents<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Make sure to test it on a copy of your workbook.

 

[Adam1979]

I'm afraid not, I copied in to the worksheet's VBA, saved it and when editing G,H & J it now gives me a runtime error "1004" stating that I cannot edit part of a merged cell (none of these cells are merged FYI)

 

[Smitty]

Some part of it must be merged or you wouldn't get that error.

You can use the individual cells though:

Cells(c.Row, "G").ClearContents
Cells(c.Row, "H").ClearContents
Cells(c.Row, "I").ClearContents
Cells(c.Row, "J").ClearContents

 

[Adam1979]

How would this look if I were to do that? Could you type it in the copy & past format like earlier:

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
End Sub


If this does'nt work, how could I attach this worksheet for you to look at it as it may explain better what I am trying to do?

Thanks

 

[Smitty]

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

 

[Adam1979]

I did actually have J,K&L merged, I just had a blind moment. I have changed it so they are no longer merged anywhere and copied that code in to the VBA. It now doesn't give me an error, it just calculates and calculates and calculates without doing anything. Infact I have to use the task manager to force a close.

Could I email this to you unless you have any more suggestions?

Thanks for your help here.

 

[Smitty]

Try adding Application.EnableEvents = False at the beginning of the code. Just make sure to set it back to true at the end.