VBA - Clear one cell if another one changes in the same row

[Paula03]

Hey everybody,

The goal is to clear the cell in column G if I put an x in J.
(I have another sheet where all workers are listed and it's supposed to show a worker as busy if he is mentioned in this sheet. Have problems with this one too, but thats for another day)

I've been working with the following code:

'** Change Cell Contens in column G if column J changes
If Not Intersect(Target, Range("J3:J100")) Is Nothing Then
Application.EnableEvents = False
Range("G3:G100").ClearContents
Application.EnableEvents = True
End If
End Sub

It works, but I have a problem.
If I make a change in any cell of column J it clears all cells in column G.
However I only want the clearing of a cell in G to happen in the specific row where the cell in J is changed. Not the whole column.
I hope this makes sense.

Thanks in advance!

 

[My Aswer Is This]

You said " if I put an x in J"
But your code does not say x.
This script runs if you change the cell value.

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("J3:J100")) Is Nothing Then
Target.Offset(, -3).Value = ""
End If
End Sub

 

[Paula03]

You said " if I put an x in J"
But your code does not say x.
This script runs if you change the cell value.

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("J3:J100")) Is Nothing Then
Target.Offset(, -3).Value = ""
End If
End Sub

Hello,

please excuse the late reply.
I didn't put the x in the original code, because it was supposed to react no matte what I put in J.

Thank you fot the help.
Your code works, but now it clears both cell G and J in the row if i put an x in J.
The thing is, I need the x I put in cell J to stay for conditional formatting and other formulas.

I only want to clear cell G.

Do you happen to know why it now clears both cells?

Thanks a lot in advance!!! :D

 

[Joe4]

Your code works, but now it clears both cell G and J in the row if i put an x in J.
The thing is, I need the x I put in cell J to stay for conditional formatting and other formulas.

There is nothing in the code they posted that would clear the "x" in column J.

If it is being cleared, I suspect that you mayhave some other VBA code that is doing that.
Do you have any other VBA code on this sheet? If so, could you please post it?

Or, is this part of a table?
If so, does column J have any formulas in it, or are all the values hard-coded?

 

[Paula03]

There is nothing in the code they posted that would clear the "x" in column J.

If it is being cleared, I suspect that you mayhave some other VBA code that is doing that.
Do you have any other VBA code on this sheet? If so, could you please post it?

Or, is this part of a table?
If so, does column J have any formulas in it, or are all the values hard-coded?

Hello,

thanks for your input.

This is the whole code for the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
'** Mehrfachauswahl über DropDown-Liste (Gültigkeitsprüfung)
'** Einfügen im Code-Container des betreffenden Arbeitsblattes
 
'** Dimensionierung der Variablen
Dim rngDV As Range
Dim wert_old As String
Dim wertnew As String
 
'** Errorhandling
On Error GoTo Errorhandling
 
'** Mehrfachauswahl im definierten Bereich (Bsp. B4:B14) durchführen
If Not Application.Intersect(Target, Range("G3:G100")) Is Nothing Then
 
  '**Range definieren
  Set rngDV = Target.SpecialCells(xlCellTypeAllValidation)
  If rngDV Is Nothing Then GoTo Errorhandling
  
  '** Prüfen, ob eine gültige Zelle ausgewählt wurde und Werte eintragen
  If Not Application.Intersect(Target, rngDV) Is Nothing Then
    Application.EnableEvents = False
    wertnew = Target.Value
    Application.Undo
    wertold = Target.Value
    Target.Value = wertnew
    If wertold <> "" Then
      If wertnew <> "" Then
        Target.Value = wertold & ", " & wertnew
      End If
    End If
  End If
  Application.EnableEvents = True
End If

Errorhandling:
Application.EnableEvents = True

'** Change Cell Contens in column G if column J changes
If Not Intersect(Target, Range("J3:J100")) Is Nothing Then
Target.Offset(, -3).Value = ""
 End If
End Sub

The larger part of the code is to allow multiple selection from a drop down in column G.
There are no formulas in J.

It's all part of a table.
The table will be used to appoint tasks/to dos to workers.

There is a separate sheet where it shows if a worker is "busy" with a task or "free" and can get a new task.
That is why I want to clear G if a task is done, so it shows the worker is free for the next task.

Heres a pic of the table:

I hope this helps.

I really appreciate you guys hepling!
It helps me a lot at getting better with VBA and Excel in general.

 

[Alex Blakenburg]

Can you try moving the Errorhandling to the end of the code and making the other changes to the new code that I have here:

'** Change Cell Contens in column G if column J changes
If Not Intersect(target, Range("J3:J100")) Is Nothing Then
    If LCase(target.Value) = "x" Then
        Application.EnableEvents = False
        target.Offset(, -3).Value = ""
        Application.EnableEvents = True
    End If
End If

Exit Sub

Errorhandling:
Application.EnableEvents = True

 

[Paula03]

Can you try moving the Errorhandling to the end of the code and making the other changes to the new code that I have here:

'** Change Cell Contens in column G if column J changes
If Not Intersect(target, Range("J3:J100")) Is Nothing Then
    If LCase(target.Value) = "x" Then
        Application.EnableEvents = False
        target.Offset(, -3).Value = ""
        Application.EnableEvents = True
    End If
End If

Exit Sub

Errorhandling:
Application.EnableEvents = True

It worked, exept I had to change the line If LCase(target.Value) = "x" Then to If Target.Value = "x" Then.

Thank you very much.
@Alex Blakenburg @Joe4 @My Aswer Is This y'all rock!

 

[Alex Blakenburg]

Thanks for the feedback. Glad we could help.