PDA

View Full Version : Solved: Clear corresponding cells when changing one cell in a row



Skopweb
08-20-2009, 11:16 PM
Hello
I have the following script that helps me to clear cells C3 and D3 if i change the value in cell B3
Is there a way i can apply this to the entire coloumn and clear its corresponding cell for that row
--------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then
Application.EnableEvents = False
If Target.Value = "Yes" Then
With Range("$C$3")
.Value = ""
End With
With Range("$D$3")
.Value = ""
End With
Else
With Range("$C$3")
.Value = ""
End With
With Range("$D$3")
.Value = ""
End With
End If
End If
Application.EnableEvents = True
End Sub
--------------------------

GTO
08-20-2009, 11:25 PM
Greetings Skopweb,

In your current code, if the val of B3 is changed (including if B3 is cleared), C3: D3 get cleared. Is this what you want applied to whichever row we're in, if the cell in Col B gets cleared?

Mark

Skopweb
08-20-2009, 11:31 PM
Hello GTO
The cell B3 has a drop down list and if i select any other value in the list of B3 it will clear its corresponding cells in C3 and D3
I need this to occur for the entire B C and D column.. with column B having a similar dropdown list in every cell :think:

GTO
08-20-2009, 11:45 PM
Not sure if I am understanding clearly. Please try in a junk copy of your wb.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

'// Check to see that we're changing a cell in Col B(2) and that we're only //
'// changing one cell (not clearing a range or something). //
If Target.Column = 2 _
And Not Target.Count > 1 Then
Application.EnableEvents = False
Range("C" & Target.Row & ":D" & Target.Row).ClearContents
Application.EnableEvents = True
End If
End Sub


Mark

Skopweb
08-21-2009, 12:45 AM
Mark ...... U R A WIZARD
this is what i needed .....
i'm still a learner for VB
Thanks a million :bow: :beerchug: :bow: